Search Results

Search found 4815 results on 193 pages for 'parameterized queries'.

Page 171/193 | < Previous Page | 167 168 169 170 171 172 173 174 175 176 177 178  | Next Page >

• Is it possible to load an entire SQL Server CE database into RAM?

  - by DanM

  I'm using LinqToSql to query a small SQL Server CE database. I've noticed that any operations involving sub-properties are disappointingly slow. For example, if I have a Customer table that is referenced by an Order table via a foreign key, LinqToSql will automatically create an EntitySet<Order> property. This is a nice convenience, allowing me to do things like Customer.Order.Where(o => o.ProductName = "Stopwatch"), but for some reason, SQL Server CE hangs up pretty bad when I try to do stuff like this. One of my queries, which isn't really that complicated takes 3-4 seconds to complete. I can get the speed up to acceptable, even fast, if I just grab the two tables individually and convert them to List<Customer> and List<Order>, then join then manually with my own query, but this is throwing out a lot of the appeal of LinqToSql. So, I'm wondering if I can somehow get the whole database into RAM and just query that way, then occasionally save it. Is this possible? How? If not, is there anything else I can do to boost the performance? Note: My database in its initial state is about 250K and I don't expect it to grow to more than 1-2Mb. So, loading the data into RAM certainly wouldn't be a problem from a memory point of view.

  Read the article

• How can I make hundreds of simultaneously running processes communicate with a database through one

  - by Olfan

  Long speech short: How can I make hundreds of simultaneously running processes communicate with a database through one or few permanent sessions? The whole story: I once built a number crunching engine that handles vast amounts of large data files by forking off one child after another giving each a small number of files to work on. File locking, progress monitoring and result propagation happen in an Oracle database which all (sub-)processes access at various times using an application-specific module which encapsulates DBI. This worked well at first, but now with higher volumes of input data, the number of database sessions (one per child, and they can be very short-lived) constantly being opened and closed is becoming an issue. I now want to centralise database access so that there are only one or few fixed database sessions which handle all database access for all the (sub-)processes. The presence of the database abstraction module should make the changes easy because the function calls in the worker instances can stay the same. My problem is that I cannot think of a suitable way to enhance said module in order to establish communication between all the processes and the database connector(s). I thought of message queueing, but couldn't come up with a way of connecting a large herd of requestors with one or few database connectors in a way so that bidirectional communication is possible (for collecting the query result). An asynchronous approach could help here in that all requests are written to the same queue and the database connector servicing the request will "call back" to submit the result. But my mind fails me in generating an image clear enough so that I can paint into code. Threading instead of forking might have given me an easier start, but this would now require massive changes to the code base that I'm not prepared to do to a live system. The more I think of it, the more the base idea looks like a pre-forked web server to me only that it doesn't serve web pages but database queries. Any ideas on what to dig into, and where? Sample (pseudo) code to inspire me, links to possibly related articles, ready solutions on CPAN maybe?

  Read the article

• newbie hibernate first level cache confusion

  - by Bruce

  Hi all I'm just geting to grips with hibernate. Little bit confused. I just wanted to watch the operation of the first level cache, which I understood to batch up queries until the end of the session. But if I create an object, hibernate saves it immediately, so that when I later update it in the same transaction, it has to do an update too: Session session = factory.getCurrentSession(); session.beginTransaction(); Test1 test1 = new Test1(); test1.setName("Test 1"); test1.setValue(10); // Touch it session.save(test1); System.out.println("At checkpoint 1"); test1.setValue(20); session.getTransaction().commit(); I see the sql for the save, then 'At checkpoint 1', then the sql for the update. Do I have something set up wrong or am I misunderstanding hibernate's first level cache? Is there a good document on the first level cache - I didn't find anything in the hibernate docs, but I could easily have missed it.. Thanks!

  Read the article

• MySQL query paralyzes site

  - by nute

  Once in a while, at random intervals, our website gets completely paralyzed. Looking at SHOW FULL PROCESSLIST;, I've noticed that when this happens, there is a specific query that is "Copying to tmp table" for a loooong time (sometimes 350 seconds), and almost all the other queries are "Locked". The part I don't understand is that 90% of the time, this query runs fine. I see it going through in the process list and it finishes pretty quickly most of the time. This query is being called by an ajax call on our homepage to display product recommendations based your browsing history (a la amazon). Just sometimes, randomly (but too often), it gets stuck at "copying to tmp table". Here is a caught instance of the query that was up 109 seconds when I looked: SELECT DISTINCT product_product.id, product_product.name, product_product.retailprice, product_product.imageurl, product_product.thumbnailurl, product_product.msrp FROM product_product, product_xref, product_viewhistory WHERE ( (product_viewhistory.productId = product_xref.product_id_1 AND product_xref.product_id_2 = product_product.id) OR (product_viewhistory.productId = product_xref.product_id_2 AND product_xref.product_id_1 = product_product.id) ) AND product_product.outofstock='N' AND product_viewhistory.cookieId = '188af1efad392c2adf82' AND product_viewhistory.productId IN (24976, 25873, 26067, 26073, 44949, 16209, 70528, 69784, 75171, 75172) ORDER BY product_xref.hits DESC LIMIT 10 Of course the "cookieId" and the list of "productId" changes dynamically depending on the request. I use php with PDO.

  Read the article

• Java's Swing Threading

  - by nevets1219

  My understanding is that if I start up another thread to perform some actions, I would need to SwingUtilities.invokeAndWait or SwingUtilities.invokeLater to update the GUI while I'm in said thread. Please correct me if I'm wrong. What I'm trying to accomplish is relatively straightforward: when the user clicks submit, I want to (before performing any actions) disable the submit button, perform the action, and at the end of the action re-enable the button. My method to perform the action updates the GUI directly (displays results) when it gets the results back. This action basically queries a server and gets some results back. What I have so far is: boolean isRunning = false; synchronized handleButtonClick() { if ( isRunning == false ) { button.setEnabled( false ); isRunning = true; doAction(); } } doAction() { new Thread() { try { doAction(); // Concern A } catch ( ... ) { displayStackTrace( ... ); // Concern B } finally { SwingUtilities.invokeLater ( /* simple Runnable to enable button */ ); isRunning = false; } } } For both of my concerns above, do I would have to use SwingUtilities.invokeAndWait since they both will update the GUI? All GUI updates revolve around updating JTextPane. Do I need to in my thread check if I'm on EDT and if so I can call my code (regardless of whether it updates the GUI or not) and NOT use SwingUtilities.invokeAndWait?

  Read the article

• checking is username exists on two tables PHP PDO?

  - by PHPLOVER

  Me again. I have a users table and a users_banlist table. On my registration form i want to check all in one query whether the username someone entered on form exists in the users table and see if it also exists on the users_banlist table. I can do them on there own in individual queries but would rather do it all in one. Here is what i got, but even thou i enter a username that is taken it does not tell me its already taken. $stmt = $dbh->prepare(" SELECT users.user_login, users_banlist.user_banlist FROM users , users_banlist WHERE users.user_login = ? OR users_banlist.user_banlist = ?"); // checker if username exists in users table or users_banlist table $stmt->execute(array($username, $username)); if ( $stmt->rowCount() > 0 ) { $error[] = 'Username already taken'; } Basically i think it is something to do with the execute or rowCount(), could anyone tell me where i am going wrong ? being new to pdo im finding it a little confusing at the moment until i get my pdo book delivered to learn pdo. Thank you as always, phplover

  Read the article

• Sql Server 2005 Check Constraint not being applied in execution when using variables

  - by DarylS

  Here is some SQL sample code: --Create 2 Sales tables with constraints based on the saledate create table Sales1(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales1 ADD CONSTRAINT CK_Sales1 CHECK (([SaleDate]>='01 May 2010')) GO create table Sales2(SaleDate datetime, Amount money) ALTER TABLE dbo.Sales2 ADD CONSTRAINT CK_Sales2 CHECK (([SaleDate]<'01 May 2010')) GO --Insert some data into Sales1 insert into Sales1 (SaleDate, Amount) values ('02 May 2010', 50) insert into Sales1 (SaleDate, Amount) values ('03 May 2010', 60) GO --Insert some data into Sales2 insert into Sales2 (SaleDate, Amount) values ('30 Mar 2010', 10) insert into Sales2 (SaleDate, Amount) values ('31 Mar 2010', 20) GO --Create a view that combines these 2 tables create VIEW [dbo].[Sales] AS SELECT SaleDate, Amount FROM Sales1 UNION ALL SELECT SaleDate, Amount FROM Sales2 GO --Get the results --Query 1 select * from Sales where SaleDate < '31 Mar 2010' -- if you look at the execution plan this query only looks at Sales2 (Which is good) --Query 2 DECLARE @SaleDate datetime SET @SaleDate = '31 Mar 2010' select * from Sales where SaleDate < @SaleDate -- if you look at the execution plan this query looks at Sales1 and Sales2 (Which is NOT good) Looking at the execution plan you will see that the two queries are differnt. For Query 1 the only table that is accessed is Sales1 (which is good). For Query 2 both tables are accessed (Which is bad). Why are these execution plans different, and how do i get Query 2 to only access the relevant table when variables are used? I have tried to add indexes for the SaleDate column and that does not seem to help.

  Read the article

• How to translate the fields of a database model?

  - by Tõnis M

  I have some tables/models in a web app that will have multilingual content. For example a university, with it's description in a default language(english) and the user wants he can see the same information in another language( if the object has it's fields translated). If there were only a few languages then I would just add fields like name_en and name_de and so on, but the number of languages isn't fixed, so that' would create a mess. I could also just create a new object with the translated data but then foreign keys wouldn't work, and only some of the fields can be translated so that would create duplicate data. Storing the translations in a file and using gettext or something similar is also not an option since the objects fields can be translated by the website user, not only developers/admins. What would be the best way to design/architect such a database? Searching from the translated data should also be not too complex - as it should not require creating complex joins which would make the queries slower I'm using PostgreSQL and Ruby of Rails but I'm not looking for a technical solution but for a general idea how to design it.

  Read the article

• How can I handle all my errors/messages in one place on an Asp.Net page?

  - by Atomiton

  Hi all, I'm looking for some guidance here. On my site I put things in Web user controls. For example, I will have a NewsItem Control, an Article Control, a ContactForm control. These will appear in various places on my site. What I'm looking for is a way for these controls to pass messages up to the Page that they exist on. I don't want to tightly couple them, so I think I will have to do this with Events/Delegates. I'm a little unclear as to how I would implement this, though. A couple of examples: 1 A contact form is submitted. After it's submitted, instead of replacing itself with a "Your mail has been sent" which limits the placement of that message, I'd like to just notify the page that the control is on with a Status message and perhaps a suggested behaviour. So, a message would include the text to render as well as an enum like DisplayAs.Popup or DisplayAs.Success 2 An Article Control queries the database for an Article object. Database returns an Exception. Custom Exception is passed to the page along with the DisplayAs.Error enum. The page handles this error and displays it wherever the errors go. I'm trying to accomplish something similar to the ValidationSummary Control, except that I want the page to be able to display the messages as the enum feels fit. Again, I don't want to tightly bind or rely a control existing on the Page. I want the controls to raise these events, but the page can ignore them if it wants. Am I going about this the right way? I'd love a code sample just to get me started. I know this is a more involved question, so I'll wait longer before voting/choosing the answers.

  Read the article

• Finding the unique paths through a Neo4j graph

  - by Larry

  I have a Neo4j graph with 12 inputs and 4 outputs, and am trying to write a query with the Java Traverser that will return the 14 unique paths from an input to an output node. All the queries I have tried return only a subset of the 14 paths. For example, the code below returns 4 paths, but the other 10 all stop 1 node short of the output. RelationshipType relType = RelationshipTypes.EDGE; TraversalDescription td = new TraversalDescriptionImpl() .depthFirst() .relationships(relType, Direction.OUTGOING); for (Node node : inputs){ Traverser tv = td.traverse(node); Iterator<Path> iter = tv.iterator(); // ... print path } I've tried uniqueness and depth settings as well, with no effect. The query below returns all 14 paths using the web interface, but when I use the ExecutionEngine class, I only get 13 paths back. START s=node(*) MATCH (s)-[p:EDGE*]->(c) WHERE s.type! = "INPUT" AND c.type! = "OUTPUT" RETURN p How do I get all the unique paths using the Java API?

  Read the article

• MySQL Datefields: duplicate or calculate?

  - by Konerak

  We are using a table with a structure imposed upon us more than 10 years ago. We are allowed to add columns, but urged not to change existing columns. Certain columns are meant to represent dates, but are put in different format. Amongst others: * CHAR(6): YYMMDD * CHAR(6): DDMMYY * CHAR(8): YYYYMMDD * CHAR(8): DDMMYYYY * DATE * DATETIME Since we now would like to do some more complex queries, using advanced date functions, my manager proposed to d*uplicate those problem columns* to a proper FORMATTED_OLDCOLUMNNAME column using a DATE or DATETIME format. Is this the way to go? Couldn't we just use the STR_TO_DATE function each time we accessed the columns? To avoid every query having to copy-paste the function, I could still work with a view or a stored procedure, but duplicating data to avoid recalculation sounds wrong. Solutions I see (I guess I prefer 2.2.1) 1. Physically duplicate columns 1.1 In the same table 1.1.1 Added by each script that does a modification (INSERT/UPDATE/REPLACE/...) 1.1.2 Maintained by a trigger on each modification 1.2 In a separate table 1.2.1 Added by each script that does a modification (INSERT/UPDATE/REPLACE/...) 1.2.2 Maintained by a trigger on each modification 2. On-demand transformation 2.1 Each query has to perform the transformation 2.1.1 Using copy-paste in the source code 2.1.2 Using a library 2.1.3 Using a STORED PROCEDURE 2.2 A view performs the transformation 2.2.1 A separate table replacing the entire table 2.2.2 A separate table just adding the date-fields for the primary keys Am I right to say it's better to recalculate than to store? And would a view be a good solution?

  Read the article

• PHP, MySQL, Memcache / Ajax Scaling Problem

  - by Jeff Andersen

  I'm building a ajax tic tac toe game in PHP/MySQL. The premise of the game is to be able to share a url like mygame.com/123 with your friends and you play multiple simultaneous games. The way I have it set up is that a file (reload.php) is being called every 3 seconds while the user is viewing their game board space. This reload.php builds their game boards and the output (html) replaces their current game board (thus showing games in which it is their turn) Initially I built it entirely with PHP/MySQL and had zero caching. A friend gave me a suggestion to try doing all of the temporary/quick read information through memcache (storing moves, and ID matchups) and then building the game boards from this information. My issue is that, both solutions encounter a wall when there is roughly 30-40 active users with roughly 40-50 games running. It is running on a VPS from VPS.net with 2 nodes. (Dedicated CPU: 1.2GHz, RAM: 752MB) Each call to reload.php peforms 3 selects and 2 insert queries. The size of the data being pulled is negligible. The same actions happen on index.php to build the boards for the initial visit. Now that the backstory is done, my question is: Would there be a bottleneck in that each user is polling the same file every 3 seconds to rebuild their gameboards, and that all users are sitting on index.php from which the AJAX calls are made within the HTML. If so, is it possible to spread the users' calls out over a set of files designated to building the game boards (eg. reload1.php 2, 3 etc) and direct users to the appropriate file. Would this relieve the pressure? A long winded explanation; however, I didn't have anywhere else to ask. Thanks very much for any insight.

  Read the article

• SYN receives RST,ACK very frequently

  - by user1289508

  Hi Socket Programming experts, I am writing a proxy server on Linux for SQL Database server running on Windows. The proxy is coded using bsd sockets and in C, and it is working just fine. When I use a database client (written in JAVA, and running on a Linux box) to fire queries (with a concurrency of 100 or more) directly to the Database server, not experiencing connection resets. But through my proxy I am experiencing many connection resets. Digging deeper I came to know that connection from 'DB client' to 'Proxy' always succeeds but when the 'Proxy' tries to connect to the DB server the connection fails, due to the SYN packet getting RST,ACK. That was to give some background. The question is : Why does sometimes SYN receives RST,ACK? 'DB client(linux)' to 'Server(windows)' ---- Works fine 'DB client(linux) to 'Proxy(Linux)' to 'Server(windows)' ----- problematic I am aware that this can happen in "connection refused" case but this definitely is not that one. SYN flooding might be another scenario, but that does not explain fine behavior while firing to Server directly. I am suspecting some socket option setting may be required, that the client does before connecting and my proxy does not. Please put some light on this. Any help (links or pointers) is most appreciated. Additional info: Wrote a C client that does concurrent connections, which takes concurrency as an argument. Here are my observations: - At 5000 concurrency and above, some connects failed with 'connection refused'. - Below 2000, it works fine. But the actual problem is observed even at a concurrency of 100 or more. Note: The problem is time dependent sometimes it never comes at all and sometimes it is very frequent and DB client (directly to server) works fine at all times .

  Read the article

• Question about architecting asp.net mvc application ?

  - by Misnomer

  I have read little bit about architecture and also patterns in order to follow the best practices. So this is the architecture we have and I wanted to know what you think of it and any proposed changes or improvements to it - Presentation Layer - Contains all the views,controllers and any helper classes that the view requires also it containes the reference to Model Layer and Business Layer. Business Project - Contains all the business logic and validation and security helper classes that are being used by it. It contains a reference to DataAccess Layer and Model Layer. Data Access Layer - Contains the actual queries being made on the entity classes(CRUD) operations on the entity classes. It contains reference to Model Layer. Model Layer - Contains the entity framework model,DTOs,Enums.Does not really have a reference to any of the above layers. What are your thoughts on the above architecture ? The problem is that I am getting confused by reading about like say the repository pattern, domain driven design and other design patterns. The architecture we have although not that strict still is relatively alright I think and does not really muddle things but I maybe wrong. I would appreciate any help or suggestions here. Thanks !

  Read the article

• Large Switch statements: Bad OOP?

  - by Mystere Man

  I've always been of the opinion that large switch statements are a symptom of bad OOP design. In the past, I've read articles that discuss this topic and they have provided altnerative OOP based approaches, typically based on polymorphism to instantiate the right object to handle the case. I'm now in a situation that has a monsterous switch statement based on a stream of data from a TCP socket in which the protocol consists of basically newline terminated command, followed by lines of data, followed by an end marker. The command can be one of 100 different commands, so I'd like to find a way to reduce this monster switch statement to something more manageable. I've done some googling to find the solutions I recall, but sadly, Google has become a wasteland of irrelevant results for many kinds of queries these days. Are there any patterns for this sort of problem? Any suggestions on possible implementations? One thought I had was to use a dictionary lookup, matching the command text to the object type to instantiate. This has the nice advantage of merely creating a new object and inserting a new command/type in the table for any new commands. However, this also has the problem of type explosion. I now need 100 new classes, plus I have to find a way to interface them cleanly to the data model. Is the "one true switch statement" really the way to go? I'd appreciate your thoughts, opinions, or comments.

  Read the article

• Grouping by date, with 0 when count() yields no lines

  - by SCO

  I'm using Postgresql 9 and I'm fighting with counting and grouping when no lines are counted. Let's assume the following schema : create table views { date_event timestamp with time zone ; event_id integer; } Let's imagine the following content : 2012-01-01 00:00:05 2 2012-01-01 01:00:05 5 2012-01-01 03:00:05 8 2012-01-01 03:00:15 20 I want to group by hour, and count the number of lines. I wish I could retrieve the following : 2012-01-01 00:00:00 1 2012-01-01 01:00:00 1 2012-01-01 02:00:00 0 2012-01-01 03:00:00 2 2012-01-01 04:00:00 0 2012-01-01 05:00:00 0 . . 2012-01-07 23:00:00 0 I mean that for each time range slot, I count the number of lines in my table whose date correspond, otherwise, I return a line with a count at zero. The following will definitely not work (will yeld only lines with counted lines 0). SELECT extract ( hour from date_event ),count(*) FROM views where date_event > '2012-01-01' and date_event <'2012-01-07' GROUP BY extract ( hour from date_event ); Please note I might also need to group by minute, or by hour, or by day, or by month, or by year (multiple queries is possible of course). I can only use plain old sql, and since my views table can be very big (100M records), I try to keep performance in mind. How can this be achieved ? Thank you !

  Read the article

• Is it possible to cache all the data in a SQL Server CE database using LinqToSql?

  - by DanM

  I'm using LinqToSql to query a small, simple SQL Server CE database. I've noticed that any operations involving sub-properties are disappointingly slow. For example, if I have a Customer table that is referenced by an Order table, LinqToSql will automatically create an EntitySet<Order> property. This is a nice convenience, allowing me to do things like Customer.Order.Where(o => o.ProductName = "Stopwatch"), but for some reason, SQL Server CE hangs up pretty bad when I try to do stuff like this. One of my queries, which isn't really that complicated takes 3-4 seconds to complete. I can get the speed up to acceptable, even fast, if I just grab the two tables individually and convert them to List<Customer> and List<Order>, then join then manually with my own query, but this is throwing out a lot of what makes LinqToSql so appealing. So, I'm wondering if I can somehow get the whole database into RAM and just query that way, then occasionally save it. Is this possible? How? If not, is there anything else I can do to boost the performance besides resorting to doing all the joins manually? Note: My database in its initial state is about 250K and I don't expect it to grow to more than 1-2Mb. So, loading the data into RAM certainly wouldn't be a problem from a memory point of view. Update Here are the table definitions for the example I used in my question: create table Order ( Id int identity(1, 1) primary key, ProductName ntext null ) create table Customer ( Id int identity(1, 1) primary key, OrderId int null references Order (Id) )

  Read the article

• MySQL problem reconnecting (mysqld.exe) keeps giving error...

  - by Ronedog

  Need some guidance figuring out what went wrong. I've been using mysql, phpmyadmin for just under a year on my home computer while I develop a webapp. 3 days ago I updated my windows vista with all the "wonderful" microsoft updates, security patches, etc...and now it's broke. I tried uninstalling all the upgrades, but there are 4 of them I can't unistall because microsoft says their "operating system" updates and can't be unistalled. My system is: windows vista, php 5+, mysql 5.1, Apache 2+. I can run my web app and it queries the database without any problems. However, when I run phpmyadmin to get into the database I get an error: "mysqld.exe has stopped working" and phpmyadmin crashes. I tried going to the command line for mysql to do a mysqldump to backup my database and it gives me an error "2013, could not connect to the server". If I restart the computer the webapp will work again. Basically, php can query the database, but if I try to get at the database through phpmyadmin, or the command prompt the mysqld.exe error occurs and blows mysql out. Any ideas what's going on here? Any ideas how to get around this to backup the db, so I can reinstall mysql?. I'm really lost where to start. I don't really know if the updates caused the problem, or if the 4 updates that can't be unistalled are really the problem. Any tips will be appreciated. thanks.

  Read the article

• Custom PHP Framework Feedback

  - by Jascha

  I've been learning OOP programming for about a year and a half now and have developed a fairly standard framework to which I generally abide by. I'd love some feedback or input on how I might improve some functionality or if there are some things I'm overlooking. VIEW MODE 1) Essentially everything starts at the Index.php page. The first thing I do is require my "packages.php" file that is basically a config file that imports all of the classes and function lists I'll be using. 2) I have no direct communication between my index.php file and my classes, what I've done is "pretty them up" with my viewfunctions.php file which is essentially just a conduit to the classes so that in my html I can write <?php get_title('page'); ?> instead of <?php echo $pageClass->get_title('page'); ?> Plus, I can run a couple small booleans and what not in the view function script that can better tailor the output of the class. 3) Any information brought in via the database is started from it's corresponding class that has direct communication with the database class, the only class that is allowed direct to communicate with the database (allowed in the sense that I run all of my queries with custom class code). INPUT MODE 1) Any user input is sent to my userFunctions.php. 2) My security class is then instantiated where I send whatever user input that has been posted for verification and validation. 3) If the input passes my security check, I then pass it to my DB class for input into my Database. FEEDBACK I'm wondering if there are any glaringly obvious pitfalls to the general structure, or ways I can improve this. Thank you in advance for your input. I know there is real no "right" answer for this, but I imagine a couple up votes would be in order for some strong advice regarding building frameworks. -J

  Read the article

• how do I create a custom route in rails where I pass the id of an existing Model?

  - by Angela

  I created the following route: map.todo "todo/today", :controller => "todo", :action => "show_date" Originally, the 'show_date' action and associated view would display all the activities associated for that day for all the Campaigns. This ended up being very slow on the database...it would generate roughly 30 records but was still slow. So, I'm thinking of creating a partial that would first list the campaigns separately. If someone clicked on a link associated with campaign_id = 1, I want it to go to the following route: todo/today/campaign/1 Then I would like to know how to know that the '1' is the campaign_id in the controller and then just do its thing. The reason I want a distinct URL is so that I can cache this list. I have to keep going back to this and it's slow. NOTE: It's possibly the problem actually is that I've written the queries in a slow way and sqlite isn't representative of how it will be in production, in which case this work-around is unnecessary, but right now, I need a way to get back to the whole list quickly.

  Read the article

• I read 3 pages of a JQuery book and here's my reaction and question

  - by George

  My jQuery reaction to the language's flexible "selectors" is probably rooted in this experience: I once had managed a project where a developer constructed a web page that was used by users to provide very flexible search parameters for a search screen using dynamic sql string building based on the user's specified search parameter. The resulting queries were usually very complicated and involved joins to many tables. One of the options that the user had was to choose from one of 3 an options. Depending on the user's choice for this option, the resulting SQL would need to query a different set of database columns. For example, if choice option "A" were selected, the resulting database columns queried would be prefixed with "A_"; if option "B" were selected, he resulting database columns queried would be prefixed with "B_" and so on. The developer choice to write all the complete SQL assuming that the user selected, for example, option "A" and therefore first constructed SQLs of this type: SQL = "SELECT A_COL1, A_COL2, A_COL3 FROM TABLE ..." and then after constructing one of a million possible variations on the Query From Hell, did something like this: If UserOption = "B" then SQL = SQL.Replace("A_","B_") 'replace everywhere End if He insisted that this was the easiest was to code it, and while I understood that, I was concerned about maintenance of this code. You see, this worked for a while, but as the search options grew and the database columns evolved, the various "REPLACE small substring" with another small substring had unexpected consequences when applied to an evolving database and new search options. My feeling is that code should be written as much as possible such that you can add to it without fear of breaking what is already there. I feel a better approach, though a bit more work, would have been to write a function to return the appropriate target column based on a common set name and the user selected option. OK, so what does this have to do with jQuery selectors? Are the ultra flexible JQuery selectors kind of like perform a "replace all" on a SQL string? Handy as hell but potentially creating a maintenance nightmare?

  Read the article

• Speed up csv export when using php from mysql database query

  - by John

  Ok, so i've got a web system (built on codeigniter & running on mysql) that allows people to query a database of postal address data by making selections in a series of forms until they arrive at the selection that want, pretty standard stuff. They can then buy that information and download it via that system. The queries run very fast, but when it comes to applying that query to the database,and exporting it to csv, once the datasets get to around the 30,000 record mark (each row has around 40 columns of which about 20 are all populated with on average 20 chars of data per cell) it can take 5 or so minutes to export to csv. So, my question is, what is the main cause for the slowness? Is it that the resultset of data from the query is so large, that it is running into memory issues? Therefore should i allow much more memory to the process? Or, is there a much more efficient way of exporting to csv from a mysql query that i'm not doing? Should i save the contents of the query to a temp table and simply export the temp table to csv? Or am i going about this all wrong? Also, is the fact that i'm using Codeigniters Active Record for this prohibitive due to the way that it stores the resultset? Any advice is welcome! Thank you for reading!

  Read the article

• Why is only 2 of my querys working? (PHP/Mysql)

  - by ggfan

  When I remove a posting, I want it to remove the posting itself, any comments related to it, and like/dislike of the comments, and favorites that people added. So there are 4 queries that do this, but somehow only 2 of the 4 are working. When I switch say query 2 for query 3, then query 2 works and query 3 doesn't... The table names are correct and it is getting the data via $_GET. // Grab the data from the GET $posting_id = $_GET['posting_id']; // Delete the posting from the database $query = "DELETE FROM posting WHERE posting_id=$posting_id LIMIT 1"; mysqli_query($dbc, $query) or die(mysql_error()); // Delete the comments $query2 = "DELETE FROM comments WHERE posting_id=$posting_id "; mysqli_query($dbc, $query2) or die(mysql_error()); // Delete the like/dislike $query3 = "DELETE FROM comment_likedislike WHERE posting_id=$posting_id "; mysqli_query($dbc, $query3) or die(mysql_error()); // Delete the favorites $query4 = "DELETE FROM favorite WHERE posting_id=$posting_id "; mysqli_query($dbc, $query4) or die(mysql_error());

  Read the article

• How to update MySQL table with a cumulative count of events with the same date

  - by John

  I have a table which I pull data from. This data has a date and a stock symbol. There can be multiple rows of data with the same date and different stock symbols. I need to update the table so that there is a running total for all rows with the same date. When a new date occurs the cumulative count re-sets to 1 and then resumes. A typical query: mysql> SELECT Sym 'sym' , fdate as 'FilledDate', TsTradeNum 'numT' -> FROM trades_entered_b2 -> WHERE fDate >= '2009-08-03' AND fDate <= '2009-08-07' -> LIMIT 10; +------+------------+------+ | sym | FilledDate | numT | +------+------------+------+ | WAT | 2009-08-03 | 0 | | ALGN | 2009-08-04 | 0 | | POT | 2009-08-05 | 0 | | PTR | 2009-08-06 | 0 | | SCHW | 2009-08-06 | 0 | | FDO | 2009-08-07 | 0 | | NBL | 2009-08-07 | 0 | | RRC | 2009-08-07 | 0 | | WAT | 2009-08-08 | 0 | | COCO | 2009-08-08 | 0 | +------+------------+------+ What I want: +------+------------+------+ | sym | FilledDate | numT | +------+------------+------+ | WAT | 2009-08-03 | 1 | | ALGN | 2009-08-04 | 1 | | POT | 2009-08-05 | 1 | | PTR | 2009-08-06 | 1 | | SCHW | 2009-08-06 | 2 | | FDO | 2009-08-07 | 3 | | NBL | 2009-08-07 | 4 | | RRC | 2009-08-07 | 5 | | WAT | 2009-08-08 | 1 | | COCO | 2009-08-08 | 2 | +------+------------+------+ What I need to do is update the TsTradeNum column with the correct values. I have tried to create a function and various queries all failed. Any ideas? Thanks in advance

  Read the article

• MySQL: optimization of table (indexing, foreign key) with no primary keys

  - by Haradzieniec

  Each member has 0 or more orders. Each order contains at least 1 item. memberid - varchar, not integer - that's OK (please do not mention that's not very good, I can't change it). So, thera 3 tables: members, orders and order_items. Orders and order_items are below: CREATE TABLE `orders` ( `orderid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `memberid` VARCHAR( 20 ), `Time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP , `info` VARCHAR( 3200 ) NULL , PRIMARY KEY (orderid) , FOREIGN KEY (memberid) REFERENCES members(memberid) ) ENGINE = InnoDB; CREATE TABLE `order_items` ( `orderid` INT(11) UNSIGNED NOT NULL, `item_number_in_cart` tinyint(1) NOT NULL , --- 5 items in cart= 5 rows `price` DECIMAL (6,2) NOT NULL, FOREIGN KEY (orderid) REFERENCES orders(orderid) ) ENGINE = InnoDB; So, order_items table looks like: orderid - item_number_in_cart - price: ... 1000456 - 1 - 24.99 1000456 - 2 - 39.99 1000456 - 3 - 4.99 1000456 - 4 - 17.97 1000457 - 1 - 20.00 1000458 - 1 - 99.99 1000459 - 1 - 2.99 1000459 - 2 - 69.99 1000460 - 1 - 4.99 ... As you see, order_items table has no primary keys (and I think there is no sense to create an auto_increment id for this table, because once we want to extract data, we always extract it as WHERE orderid='1000456' order by item_number_in_card asc - the whole block, id woudn't be helpful in queries). Once data is inserted into order_items, it's not UPDATEd, just SELECTed. The questions are: I think it's a good idea to put index on item_number_in_cart. Could anybody please confirm that? Is there anything else I have to do with order_items to increase the performance, or that looks pretty good? I could miss something because I'm a newbie. Thank you in advance.

  Read the article

< Previous Page | 167 168 169 170 171 172 173 174 175 176 177 178  | Next Page >