March 19, 2008 at 11:47 am
hi guys i have two questions, i have a lquery, this query is basically selecting columns from various tables (overall 12 tables) and the filter is
declare @orderdate datetime
set @orderdate= '1/01/2006'
I have two questions: one, is there a way to optimize this query? i read is not good to filter out by date?
this is what i have in my query filter (orderdate is an index in the tblorders)
WHERE
tblorders.orderdate > @orderdate
my second question is that since i have a lot columns selected from different tables is it good to put the with (NOLOCK) in each select? this made my query go from 1 minute to 40 seconds.
March 19, 2008 at 12:13 pm
First, we can't help you optimize somethig we haven't seen (ie the query). Second it would also be necessary to see the DDL of the tables, have some sample data (in the form of unioned insert statements) for each table. With that, I am confident someone would be more than willing to help you out.
😎
March 19, 2008 at 2:18 pm
thank you i will.. for now can you just tell me if putting the with (NOLOCK) for each select statemment is good? this is only a select query, no updates.
March 20, 2008 at 12:54 am
Only if you don't mind potentially getting wrong data from time to time.
Nolock allows reading of uncommitted data. Also, because of the way it's implemented, there's a chance of missing rows or reading rows twice (or more) if you're querying a table that's getting updated or inserted.
If the table's not being updated or inserted, why would you need nolock.
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
March 20, 2008 at 7:24 am
Worth mentioning, did you look at the execution plan?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 20, 2008 at 9:06 am
the execution plan shows a couple of parallelism cost 1% and a couple of hash match cost 2%. No index scans.
March 20, 2008 at 9:09 am
Since we're still guessing (no specifics to work on) - can you make that a covering index? Is the index even being used by the execution plan?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 10:42 am
DBA (3/20/2008)
the execution plan shows a couple of parallelism cost 1% and a couple of hash match cost 2%. No index scans.
That's 3% of the cost. The other 97%?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 25, 2008 at 12:03 am
Can you post the execution plan for us please (saved as a .sqlplan file, zipped and attached)?
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply