which one is faster???
-
SELECT Company FROM Orders -or- SELECT DISTINCT Company FROM Orders Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways? Thanks guys! :) --------------- Concentrating on Ideas http://www.edovia.com
-
SELECT Company FROM Orders -or- SELECT DISTINCT Company FROM Orders Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways? Thanks guys! :) --------------- Concentrating on Ideas http://www.edovia.com
SELECT Company FROM Orderswill list every record, IOW ABC XYZ ABC EFG GHI XYZSELECT DISTINCT Company FROM Orderswill list only unique matches IOW ABC XYZ EFG GHI Have a look at the SQL Server SELECT statement article, nice ;) "There are no stupid question's, just stupid people." -
SELECT Company FROM Orderswill list every record, IOW ABC XYZ ABC EFG GHI XYZSELECT DISTINCT Company FROM Orderswill list only unique matches IOW ABC XYZ EFG GHI Have a look at the SQL Server SELECT statement article, nice ;) "There are no stupid question's, just stupid people."Thanks! I guess the real question is how to prevent the complete parsing of the records. Is there a way to tell SQL to "find the first occurence then stop"? Luc --------------- Concentrating on Ideas http://www.edovia.com
-
SELECT Company FROM Orders -or- SELECT DISTINCT Company FROM Orders Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways? Thanks guys! :) --------------- Concentrating on Ideas http://www.edovia.com
Well, this depends on your data. Speaking from a purely SQL Server perspective the basic SELECT is the fastest, but if the DISTINCT clause eliminates a large amount of records it might be faster when taking the middle tier, network access, aso (basically the rest of your processing...) into consideration. The thing to remember is that a DISTINCT is post processed. So the SQL Server actually makes a complete SELECT first, and then filters the records. Morty
-
Well, this depends on your data. Speaking from a purely SQL Server perspective the basic SELECT is the fastest, but if the DISTINCT clause eliminates a large amount of records it might be faster when taking the middle tier, network access, aso (basically the rest of your processing...) into consideration. The thing to remember is that a DISTINCT is post processed. So the SQL Server actually makes a complete SELECT first, and then filters the records. Morty
Here's what I want to do: I have a table that contains words and there are no duplicates. Right now, this is what I do: SELECT * FROM table WHERE word = 'hello'; This will cause the SQL server to parse the entire table to look for records that match the criteria. But since I'm 100% sure that the word 'hello' is only there once in the table, I want the query to stop as soon as it finds the record. What can I use for this kind of job? Thanks for your help! :) --------------- Concentrating on Ideas http://www.edovia.com
-
Here's what I want to do: I have a table that contains words and there are no duplicates. Right now, this is what I do: SELECT * FROM table WHERE word = 'hello'; This will cause the SQL server to parse the entire table to look for records that match the criteria. But since I'm 100% sure that the word 'hello' is only there once in the table, I want the query to stop as soon as it finds the record. What can I use for this kind of job? Thanks for your help! :) --------------- Concentrating on Ideas http://www.edovia.com
LukeV wrote: What can I use for this kind of job? I think what you're looking for is "SELECT TOP 1" but I'm not an expert. Give it a go. Paul I think there're pieces of me you've never seen - Tori Amos, Tear in Your Hand
-
Here's what I want to do: I have a table that contains words and there are no duplicates. Right now, this is what I do: SELECT * FROM table WHERE word = 'hello'; This will cause the SQL server to parse the entire table to look for records that match the criteria. But since I'm 100% sure that the word 'hello' is only there once in the table, I want the query to stop as soon as it finds the record. What can I use for this kind of job? Thanks for your help! :) --------------- Concentrating on Ideas http://www.edovia.com
If the 'word' column is indexed as unique, SQL Server will not have to look through the entire table and will only need to search the index (very fast) to find the desired row. If the 'word' column is indexed as non-unique, SQL server will still search the index, but may need to return more than one row. If the 'word' column is not indexed SQL server will do a table-scan (full table search) and return every match. You can use the SELECT TOP 1 bla.bla.bla syntax, but probably what you want is for SQL Server to have a unique index on the 'word' column.
-
SELECT Company FROM Orders -or- SELECT DISTINCT Company FROM Orders Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways? Thanks guys! :) --------------- Concentrating on Ideas http://www.edovia.com
If you're using SQL Server you can use Query Analyzer to find out how long it took to run the query, and where the greatest cost was incurred. It's an incredibly useful tool. -- Paul "I need the secure packaging of Jockeys. My boys need a house!" - Kramer, in "The Chinese Woman" episode of Seinfeld MS Messenger: paul@oobaloo.co.uk Sonork: 100.22446
-
SELECT Company FROM Orders -or- SELECT DISTINCT Company FROM Orders Is there any speed gain when using DISTINCT? Does DISTINCT works like Find First... or it goes through all the records anyways? Thanks guys! :) --------------- Concentrating on Ideas http://www.edovia.com
DISTINCT is in general slow (depends on actual data of course). Whenever you use DISTINCT, SQL server needs to group result set (by sorting them) on DISTINCT columns. In case there's no index on those columns, it will take time. On the other hand, if result set is huge, but it contains a lot of repetitions on DISTINCT columns, eliminating repetitions may have positive effect on speed of transferring filtered results to the client. Vagif Abilov MCP (Visual C++) Oslo, Norway Hex is for sissies. Real men use binary. And the most hardcore types use only zeros - uppercase zeros and lowercase zeros. Tomasz Sowinski