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 |
|
sagitariusmzi
Posting Yak Master
113 Posts |
 Posted - 2012-02-02 : 00:36:21
|
| I have this structure of the tablesendDatatrans_id uniqueidentifierassignSend_user_id uniqueidentifierassignRec_user_id uniqueidentifierLocationImageslocation_id uniqueidentifiertrans_id uniqueidentifierLocationlocation_id uniqueidentifierlocation_name nvarcharSELECT DISTINCT T.location_id, location_name FROM Location T WITH ( NOLOCK ) INNER JOIN LocationImages TI WITH ( NOLOCK ) ON T.location_id = TI.location_id INNER JOIN WorkData J WITH ( NOLOCK ) ON TI.trans_id = J.trans_id WHERE J.assignSend_user_id = '28C86344-C148-41A5-ADD1-0A6E589C0BAF' AND j.status = 2 ORDER BY location_name when i execute without DISTINCT , the data is returned within two to three seconds, and number of records returned are more than 100,000.00with distinct it takes more than two minutes, and 5 to 6 records are returned.i tried to use the group by clause , but it also returned the data in more than two minutes.is there replacement for Distinct , because i need the Distinct records.any optimization tips will be highly appreciated |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2012-02-02 : 04:57:22
|
| It is using index scan |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-02 : 05:23:56
|
| Do you know why NOLOCK is very dangerous? (If not I can explain) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-02 : 05:26:39
|
quote:
Originally posted by tkizer
Why do you have nolock hints in there? They aren't the turbo boost button like everyone thinks...
Maybe they are! Perhaps DISTINCT has to ignore the NOLOCK hint, and then sits in the queue like "normal" and waits for the lockers to block-off  Solved by RCS I expect ... but I'll wait for O/P to speak-up on use of NOLOCK first ... |
|
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2012-02-02 : 07:40:47
|
| query with NoLock hint or without NoLock performing the same.I know , it will return Dirty ReadsI just wants to increase the performance of fetching |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-02 : 09:30:48
|
| "I know , it will return Dirty Reads"Yes, but do you know what that means in real-world usage?And more importantly will those effects have any impact on the decisions of the people who read the output from the query?"I just wants to increase the performance of fetching"NOLOCK doesn't do that, it just prevents the query getting blocked by someone else (which means that your query may perform faster when someone else is doing something critical to the database, such as inserting a record that causes an index page to be split, but that also means that the results that that query returns will be garbage ... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Slow performing query