April 22, 2010 at 1:33 am
I am not sure if I should post this question in the TSQL forum.
Scenario: There are five databases - db1, db2, db3, db4, db5
In a query i need to access all five databases. I use synonyms. When I run the query in db1, it fetches results in 5 secs. When i run it in say db2, it takes 2 mins. There are a lot of freaky things being done in the query. My question is does it matter which database I am running the query in or was this probably due to load on the database?
I use nolock, readuncommitted query hints as this is for reporting. On testing repetitively, I get random results. Sometimes it fetches me results in ~5 secs in all databases.
I don't know if I have asked the right questions. Any comment will help a lot.
thanks
arjun
https://sqlroadie.com/
April 22, 2010 at 1:40 am
hey sorry folks, i was just messed up in the head after seeing my query misbehaving so badly. I ran the query simultaneously in the DBs and they fetch the results in nearly the same time.
- arjun
https://sqlroadie.com/
April 22, 2010 at 5:01 am
Reduced execution time from ~ 2 min to 2-4 secs by changing
'where scalarvaluedfunction(table.column) = @id or @id = 0 or @id is null '
to
'where @id = 0 or @id is null or scalarvaluedfunction(table.column) = @id'
PS: when @id = 0, i have to show all records and that's where it was taking time.
-arjun
https://sqlroadie.com/
April 22, 2010 at 6:10 am
It won't matter which database you run it from. Contention, despite the nolock hints, can cause things to slow down.
You do know that nolock doesn't just mean the classic "dirty read" (a value got changed but hasn't been committed, but you get the new value anyway), but it can cause duplicate or missing rows to be returned in the dataset. Be sure that missing or extra rows will not affect your business before you use nolock hints.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2010 at 6:10 am
Arjun Sivadasan (4/22/2010)
I am not sure if I should post this question in the TSQL forum.Scenario: There are five databases - db1, db2, db3, db4, db5
In a query i need to access all five databases. I use synonyms. When I run the query in db1, it fetches results in 5 secs. When i run it in say db2, it takes 2 mins. There are a lot of freaky things being done in the query. My question is does it matter which database I am running the query in or was this probably due to load on the database?
I use nolock, readuncommitted query hints as this is for reporting. On testing repetitively, I get random results. Sometimes it fetches me results in ~5 secs in all databases.
I don't know if I have asked the right questions. Any comment will help a lot.
thanks
arjun
Time taken by any query in getting results involves the system performance , and services running in background.
i did the same with linked servers.
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
April 22, 2010 at 6:24 am
Thanks Grant and Kshitij.
@Grant: What do you mean by missing rows? Rows that are there in the dataset but not present in database as a result of an update or delete statement that was in progress while the query was run?
In this case, the users want to see a pattern of search attributes or resource utilization; so I don't think the results have to be 100% accurate.
- arjun
https://sqlroadie.com/
April 22, 2010 at 6:43 am
Arjun Sivadasan (4/22/2010)
Thanks Grant and Kshitij.@Grant: What do you mean by missing rows? Rows that are there in the dataset but not present in database as a result of an update or delete statement that was in progress while the query was run?
In this case, the users want to see a pattern of search attributes or resource utilization; so I don't think the results have to be 100% accurate.
- arjun
Missing rows as in, rows that are in the database but don't get returned by your query. Same thing with duplicate rows. Rows in the database that get returned multiple times by the query. This is all possible because of the use of nolock.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2010 at 6:55 am
Grant, I had done some reading on this and I read some articles again. Looks like no one really advises the use of nolock. It will be great if you can tell me if it is OK to go ahead with it in this context.
The application is to search data that is published (publishing is in a different application) based on attributes that are defined. Then users can customize search by defining functions and so on.
This particular query is to show the administrator how folks are searching and what data they are viewing. Considering that there are going to be thousands of searches in a day, my report would not be very critical if some rows are missed out. Is it still a dirty practicse to use nolock?
thanks
arjun
https://sqlroadie.com/
April 22, 2010 at 7:04 am
Personally, I shy away from it in almost all situations. Alternatives are to enable snapshot isolation, so that you can read rows while the data is being manipulated. Or, if you're in a situation where the database is read only, set it to read only because that eliminates locking from the processing.
Assuming neither of those is possible, you simply have to discuss the situation with the people for whom you're providing the report. If they're OK with the possibility of junk data, then it's OK. If not, not.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2010 at 7:09 am
Thanks Grant. Will do.
- arjun
https://sqlroadie.com/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply