August 22, 2011 at 12:45 am
Hi,
I have a table having more than half a million records.
There's a 'application_name' field (varchar) and index is NOT created on it.
WE DON'T HAVE RIGHTS TO CREATE INDEXES...
Now I want to filter for some 600 specific application_names, linking to another table for someother fields.
When I do it using an "IN" it takes about 5 minutes to get records.
To make it bit faster I created another table (Not a temporary table) in the same DB and inserted those 600 applciation_names.
Then did a 'Inner Join' on the applications_name. Thought it would be faster than using "IN".
But it didn't work the way I thought. Infact it took 1-2 minutes more than the query using "IN".
Why so.? My second query using "Inner Join" should have been fatser no.?
Can anyone suggest me a better way ?
Thanks in advcane.
August 22, 2011 at 1:08 am
First things first , who ever is preventing you from creating indexes need to be educated. Frankly you can do more damage writing a bad SQL query than by creating a index that's not used.
It difficult to say why the performance is impacted without looking at the actual execution plan. I have a feeling a Join hint could help, but wont be sure until I can see the execution plan.
Does the column that stores the application names contain duplicate entries ?
Could you post the query that you used along with some sample data?
August 22, 2011 at 3:24 am
http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2011 at 4:41 am
Does the column that stores the application names contain duplicate entries ?
PC wise duplciates, yes there will be many.
Nothing but appplications installed on all the PCs.
August 22, 2011 at 7:13 am
As long as you're not allowed to index this column, almost every approach will take a significant amount of time since you'll need to perform a table scan (or clustered index scan).
Depending on the size of the table it'll take a while.
You could add the data needed to a staging table and index it. But this would onle help if the column of your second table would be indexed, too. Otherwise you might need to put those data in yet another staging table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply