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 |
|
jdblack
Starting Member
11 Posts |
 Posted - 2007-11-04 : 08:20:54
|
| Hi there,I've got a SQL 2005 table(DataTable) with 5.5 million rows and a clustered index on an integer column. The column is a foreign key pointing to another table (LookupTable) that associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index.I'm running equivalent select statements with slightly different where clauses, and am not seeing the performance similarities I'd expect.select * from DataTable join LookupTable on DataTable.Integer = LookupTable.Integer1. Setting the where clause to the nvarchar(50) value on the LookupTable takes 64 seconds.2. Setting the where clause to Integer = (select integer from LookupTable where nvarchar(50) = 'mysearchstring') takes 61 seconds.3. Setting the where clause to Integer = 526 takes 1 second.Is this poor design in SQL, or is it a misconfiguration? I would have expected SQL to modify my query to essentially be #3 in all cases before touching the DataTable, which would cause all three versions to take 1 second.Thanks,Jason |
|
|
evilDBA
Posting Yak Master
155 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-04 : 13:13:52
|
| "The column is a foreign key pointing to another table (LookupTable) that [b]associates the integer with an nvarchar(50) column. Both integer and nvarchar(50) columns are in a clustered index."Some DDL for the two tables would help. Otherwise I'm just guessing at a number of possible thing.Change the names of the tables/columns if there is some confidentiality issue, otherwise don't because IME something may well get lost in translationKristen |
 |
|
|
|
|
|
Poor query optimizing