query-performance

Can this SQL Statement be made to perform better?

I have a SQL statement for merging edits from one table to another. I.e. UPDATE f SET f.AUDAPLCDE = m.AUDAPLCDE, f.AUDSTF_NO = m.AUDSTF_NO, f.AUDUPD_ID = m.AUDUPD_ID, f.AUDUPDDTE = m.AUDUPDDTE, f.UNTTYP = m.UNTTYP, f.UNTSTM_NO = m.UNTSTM_NO, f.UNTIND = f.UNTIND, f.UNQ = m.UNQ, f.TRNCTL_NO = m.TRNCTL_NO, f.TRN_NO = m.TRN_NO, f...

MySQL Embedded SELECTs vs. JOINs

Is there a noticeable difference between: SELECT userid, username, userdept, (SELECT deptname FROM depts WHERE deptid=userdept) AS deptname FROM users and SELECT userid, username FROM users INNER JOIN depts ON depts.deptid=users.userdept Which one is better? ...

How to cast MySQL result to a string?

There is a query: SELECT blalist FROM blatable WHERE blafield=714 which returns a string that looks like: "2,12,29,714,543,1719". And there is another query: SELECT userid, name, surname, creditcardnum, items FROM stolencards WHERE userid IN (SELECT blalist FROM blatable WHERE blafield=714) Now that's not working. I only ma...

effect of number of projections on query performance

I am looking to improve the performance of a query which selects several columns from a table. was wondering if limiting the number of columns would have any effect on performance of the query. ...

SQL Server: Query times out when executed from web, but super-fast when executed from SSMS

I'm trying to debug the source of a SQL timeout in a web application that I maintain. I have the source code of the C# code behind, so I know exactly what code is running. I have debugged the application right down to the line that executes the SQL code that times out, and I watch the query running in SQL profiler. When this query ...

How can I track the last location of a shipment effeciently using latest date of reporting?

I need to find the latest location of each cargo item in a consignment. We mostly do this by looking at the route selected for a consignment and then finding the latest (max) time entered against nodes of this route. For example if a route has 5 nodes and we have entered timings against first 3 nodes, then the latest timing (max time) wi...

SQL Server 2005 query performance

I have the following query: select ............. from //one table and about 20 left joins// where ( ( this_.driverName like 'blah*' or this_.renterName like 'blah*' ) or exists ( select this0__.id as y0_ from ThirdPart...

SQL Server Express 2008 Stored Procedure execution time spikes periodically

I have a big stored procedure on a SQL Server 2008 Express SP2 database that gets run about every 200 ms. Normal execution time is about 50ms. What I am seeing is large inconsistencies in this run time. It will execute for while, say 50-100 times at 40-60ms which is expected, then seemingly at random the same stored procedure will tak...

Question on partial keys and an index on a database table

Suppose I have two queries on a database table. The queries are defined in terms of fields used in the query: Query1: depends on f1, f2, and f3 Query2: depends on f1, f2, f3 and f4 I remember reading somewhere that the SQL query engine (mySQL in this case) parses the index tree starting from the leftmost fields in the index. If that...

Is there a faster way to perform this mysql query?

Is there a faster way to perform this most simple query in terms of server load? I don't think I've ever tried anything other than this method: $sql = 'SELECT thing FROM table WHERE id="' . $id . '" '; $res = mysql_query($sql); $row = mysql_fetch_array($res); $thing = $row[0]; Any way to improve this? ...

Improving query performance by using views

I have a large table with 10+ millions records in a SQL Server database. The table contains certain type of data for all 50 states in the US. So if I create 50 views, one for each state, from this table, would the performance of making queries from my application be improved? Other suggestions? ...

Strange Problem With Left Join vs Subquery in Select

I have a T-SQL query that is causing performance issues. Its a chunky one but the part that seems to be causing an issue is a simple LEFT JOIN. This can be resolved by removing the left join and using a subquery in the select, however this seems unsatisfactory to me as I can't see why one works quickly and not the other. Theres not a ...

When does MS-SQL maintain table indexes?

Hi, For arguments sake, lets say it's for SQL 2005/8. I understand that when you place indexes on a table to tune SELECT statements, these indexes need maintaining during INSERT / UPDATE / DELETE actions. My main question is this: When will SQL Server maintain a table's indexes? I have many subsequent questions: I naively assume th...

Why does select statement influence query execution and performance in MySQL?

I'm encountering a strange behavior of MySQL. Query execution (i.e. the usage of indexes as shown by explain [QUERY]) and time needed for execution are dependent on the elements of the where clause. Here is a query where the problem occurs: select distinct e1.idx, el1.idx, r1.fk_cat, r2.fk_cat from ent e1, ent_leng el1, rel_c r1, _tax_...

SQL server Timeout – only happens very occasionally

Hello, I have a web application that occasionally will throw this error…. Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. When I does I am unable to connect to SQL server, even through management studio, it’s says the server timed out and c...