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 |
|
real_pearl
Posting Yak Master
106 Posts |
 Posted - 2005-01-13 : 01:26:34
|
| I've a query that executes in 1.25 mins but when I remove the conditioncast(x as varchar)+'',''+cast(y as varchar) IN(' + @xy + ')'the query's execution time reduces to only .35 seconds.Can any one help me in improving this condition?The actual table contains around 1 million recordsSample Code :set quoted_identifier offcreate table #MyTable (x int, y int)insert into #MyTableselect 5, 6 union allselect 4, 5 union allselect 3, 3 union allselect 4, 3 union allselect 4, 6 union allselect 3, 6 select * from #MyTable where x in(3,4,5) and y in(3,5,6)declare @xy varchar(8000)set @xy = "'3, 3', '4, 3', '5, 6'"exec('select * from #MyTable where cast(x as varchar)+'', ''+cast(y as varchar) IN(' + @xy + ')')drop table #MyTable |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-13 : 02:24:13
|
| If your WHERE clause has a CAST/Expression on a column then SQL has to do a table scan, and won't use indexes.Perhaps put the values (suitably manipulated) you want to match into a temporary table and JOIN that to the main table?Kristen |
 |
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2005-01-13 : 02:41:23
|
| But I receive XY as string "'3, 3', '4, 3', '5, 6'" and to put them into a temp table with x and y column will require additional processing. |
|
|
|
sreemace
Starting Member
9 Posts |
Posted - 2005-01-13 : 03:59:36
|
| I have read in one forum of using 'with(nolock)' condition... this is when we use 'like' condition for searching big database.... its like..Select * from tablename with(nolock,index(indexname))....If u have index specified in the table..One guy tried this and he responded saying there was a huge difference in performance.......Thanks & Regards,Sreejith G |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-13 : 09:43:50
|
quote:
Originally posted by real_pearl
But I receive XY as string "'3, 3', '4, 3', '5, 6'" and to put them into a temp table with x and y column will require additional processing.
parse the string and put the values into a two column temp table or table variable, and join your data to that. Key concept to understand -- when you say "additional processing", you are talking about looping through a variable stored in memory, parsing it, and adding rows to a table. If that takes more than a millisecond or two to execute, then you've got problems. Once that is done, SQL server simply processes a join between your temp table and your main table, and uses indexes (if they are created properly) to return only the rows needed.Compare that to SQL Server casting and calculating your expression and evalating the IN() operator for EVERY ROW in your table over and over and over to determine which rows to return. As an additional benefit, you can also get rid of using dynamic SQL when you do this properly.Read up on indexes and how they work, and how a condition like WHERE SomeFormula(ColumnA) = ... completely negates the advantage of using an index. If you aren't sure about this stuff, ask us, and/or read books on-line.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-13 : 10:05:21
|
quote:
Originally posted by sreemace
I have read in one forum of using 'with(nolock)' condition... this is when we use 'like' condition for searching big database.... its like..Select * from tablename with(nolock,index(indexname))....If u have index specified in the table..One guy tried this and he responded saying there was a huge difference in performance.......Thanks & Regards,Sreejith G
Hope you like uncommitted data in your result set...and what if the cardinality of the data changes and the optimizer has found a better path?I can't argue against this method enough. I've only seen once where I had to do this.Brett8-) |
|
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2005-01-14 : 01:06:52
|
| No it did not work, it took even more timeSelect * from tablename with(nolock,index(indexname)).... |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-14 : 10:02:16
|
| did you see my post?- Jeff |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2005-01-16 : 09:15:31
|
| Unless you really know what you are doing, (in which case you probably wouldn't write a non-SARGable query, you shouldn't play with the query optimizer by using hints.Also, will NOLOCK make any difference on a SELECT if it's not done in any transaction scenario, ie it's just a firehose cursor? SELECT usually doesn't lock rows anyways.Forcing an index with CASTing the data is like pulling the rug out from under the index's feet.Sarah Berger MCSD |
|
|
|
|
|
|
|
|
CAST is killing Query