Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maxxd
Starting Member
4 Posts |
 Posted - 2012-08-09 : 14:26:58
|
| I have a multi-table select query (5 inner joins off the main table for a total of 6) with a couple different filtering criteria. Nothing too complex by any stretch, and the tables are a good size but not huge (the largest table is just under a million and a half records). The main search criteria is a date range. When the start and end dates are over 2 months ago, the query completes in under a second. However, when I use last month as the range, the query will run for over two minutes and not return any data (though there's actually less data to return). It seems like the more recent the date criteria, the slower the search. We have indexes on most of the search and join fields, including the date column.Anyone ever run into this type of issue before? Any help or advice is greatly appreciated. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-09 : 14:32:04
|
| If you update stats and then run with last month, does it go fast, or slow? And when you say it returns no data...that is an incorrect result set?-Chad |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-09 : 15:16:02
|
| is your table partitioned?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
maxxd
Starting Member
4 Posts |
Posted - 2012-08-09 : 15:29:46
|
| Wow - thanks for the quick replies!@visakh16 - no, the table is not partitioned.@chadmat - we've rebuilt the indexes on the table and it runs just as slowly. And it doesn't return an incorrect result set - it times out over a php connection and I get bored and cancel the query after about 2.5 minutes in SQL Server Management Studio Express. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-09 : 15:34:18
|
| is it a filtered index by any chance?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
maxxd
Starting Member
4 Posts |
Posted - 2012-08-09 : 15:40:42
|
| It's a non-unique, non-clustered index on the tx_date field. Sorry, I'm not the db admin so I'm not sure if that actually answers your question. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-09 : 17:37:52
|
| Can you post the query, and perhaps some metadata of the tables involved, or if you can get the actual XML execution plans of the 2 queries, that would be easiest? Is it a Stored Proc, or something else? -Chad |
|
|
|
maxxd
Starting Member
4 Posts |
Posted - 2012-08-13 : 09:51:25
|
| @chadmat - I'll see what I can do about posting the query (not sure how cool that'll be with the higher-ups). It's a straight query (not an sp) that's run via php, although I was also testing in the SQL Server Management Studio for comparison. It's got to be an indexing issue, though. We have another table that tracks the same data for a different transaction type (it's actually a larger table than the one in the original query), so I switched to that one and everything flew, regardless of the date range. It's weird.Thanks to everyone for their help so far! |
|
|
|
|
|
|
|
|
SELECT query with date criteria issue