February 2, 2010 at 2:10 am
Hey you all
Sorry about the delay, internet problem.
Right!
To answer all your questions:
Gail: I have attached screenshot of the table structure, and execution plan for the following query
[font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4[/font]
Shriji: Due to the design of the table, queries will always only use datekey and instrumentkey for where clauses.
Stef: There are statistics running for the composite key (datekey, instrumentkey).
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 2:28 am
Just to add to all the info
Me and my System Administrator had a look at the server that runs SQL Server.
The Virtual Memory is set to 2GB out of the 4GB physical and the process of SQLServer is allready running at 1.7GB under normal circumstances.
In SQL Server itself the Max Memory usage is set to 2.4GB
FYI
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 2:32 am
Plan looks okay to me and the query also looks like to have completed in 5 seconds. Looks like ur server as a whole isn't healthy..If you want optimize this query further we need to see the udf used
But as I said earlier, this query is unlikely to kill ur server.
Please start your investigations as mentioned on my earlier post.
Regards,
Raj
February 2, 2010 at 2:43 am
roelofsleroux (2/2/2010)
Gail: I have attached screenshot of the table structure, and execution plan for the following query
A picture of the execution plan is near-useless. It's just a pretty picture without all the information that's in the properties and tooltips, which is what is really important. The article I referred you to explains exactly how to post an execution plan.
I also can't copy-paste text from a screenshot of a table structure. The Create table and Create index statements would be nice.
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
February 2, 2010 at 2:44 am
dbo.fnLKPInstrumentExchangeKey is used to find the ExchangeKey of an instrument whether it is JSE, LSE, NYSE.
Its basically to avoid JOIN statements. I have a suspicion that it could have and effect, but even if I change the query to the following it still has no better affect.
[font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE InstrumentKey >= 1001
[/font]or[font="Courier New"]
SELECT tblDailyData.* FROM DATAS.tblDailyData
INNER JOIN DATAS.tblInstrument ON ...
WHERE ExchangeKey = 4[/font]
[font="Courier New"]ALTER FUNCTION [dbo].[fnLKPInstrumentExchangeKey]
(@InstrumentKey INT)
/****** Returns the specied Instrument's Exchange Key ******/
RETURNS INT
AS
BEGIN
DECLARE @ExchangeKey INT
SELECT @ExchangeKey = ExchangeKey FROM DATAS.tblInstrument WHERE InstrumentKey = @InstrumentKey
RETURN ISNULL(@ExchangeKey,0)
END[/font]
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 2:49 am
GilaMonster (2/2/2010)
roelofsleroux (2/2/2010)
A picture of the execution plan is near-useless. It's just a pretty picture without all the information that's in the properties and tooltips, which is what is really important. The article I referred you to explains exactly how to post an execution plan.I also can't copy-paste text from a screenshot of a table structure. The Create table and Create index statements would be nice.
I'm running query now and will post the SAVED execution plan as soon as its done.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 2:52 am
Why are the queries using SELECT *? Do you really need every single column from the table?
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
February 2, 2010 at 2:56 am
Not necessarily. Usually the CL column is the only one 60% of our users are interested in. But the more advanced users prefer to use Op, Cl, Hi, Lo, VWAP and Vol for statistical analysis.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 3:23 am
Execution Plan for Query:
SELECT * FROM DATAS.tblDailyData
WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4 AND DateKey = 20100101
I had to to make the where clause tak date into account otherwise it would have ran for over 20min.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 3:51 am
roelofsleroux (2/2/2010)
dbo.fnLKPInstrumentExchangeKey is used to find the ExchangeKey of an instrument whether it is JSE, LSE, NYSE.Its basically to avoid JOIN statements. I have a suspicion that it could have and effect, but even if I change the query to the following it still has no better affect.
[font="Courier New"]SELECT * FROM DATAS.tblDailyData WHERE InstrumentKey >= 1001
[/font]or[font="Courier New"]
SELECT tblDailyData.* FROM DATAS.tblDailyData
INNER JOIN DATAS.tblInstrument ON ...
WHERE ExchangeKey = 4[/font]
[font="Courier New"]ALTER FUNCTION [dbo].[fnLKPInstrumentExchangeKey]
(@InstrumentKey INT)
/****** Returns the specied Instrument's Exchange Key ******/
RETURNS INT
AS
BEGIN
DECLARE @ExchangeKey INT
SELECT @ExchangeKey = ExchangeKey FROM DATAS.tblInstrument WHERE InstrumentKey = @InstrumentKey
RETURN ISNULL(@ExchangeKey,0)
END[/font]
By looking at the function code, to me, the ISNULL step is the killer. I may be wrong but we are talking about running a condition millions of times....
February 2, 2010 at 6:14 am
roelofsleroux (2/2/2010)
Its basically to avoid JOIN statements.
It may be convenient to use but all you've done is avoid having to write JOIN statements. It's still doing a join of sorts but with the extreme overhead of a scalar function that accesses data in a table. It's a form a RBAR that you could do without.
I would modify the code to do a proper join and remove this function from my DB as a start. I'd also be on the lookout for other such RBAR problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 2, 2010 at 6:40 am
You are right Jeff. removing the RBAR UDF from the query and using a simple join does make the query almost 60% faster.
Just to clarify the use of those look-up UDF's, their mostly used in T-SQL procedures to look-up values from tables with writing a repetitive SQL to look-up values. They are not normally used in queries.
Thank for the head-up. But will this explain the reason why my SQL server over-consumed hardware resources and ended up not responding?
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 6:53 am
roelofsleroux (2/2/2010)
Execution Plan for Query:SELECT * FROM DATAS.tblDailyData
WHERE dbo.fnLKPInstrumentExchangeKey(InstrumentKey) = 4 AND DateKey = 20100101
I had to to make the where clause tak date into account otherwise it would have ran for over 20min.
There's nothing that can be done for this query as it's written. The clustered index is on date and the function's not SARGable, hence there's no index in existence that will help this further as it's currently written.
What does that function do and can it be removed and replaced with appropriate joins and SARGable conditions?
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
February 2, 2010 at 7:07 am
Gila like I stated to Jeff, the function is not a must. See it as n lazy and resource expensive way of writing the query. The join is faster than the rbar function ... clearly.
As far as your opinion on the indexes goes, I'm pretty much happy with your statement that the Index on the composite key should be sufficient.
But i still have to know:
Will increasing the Memory and Processor settings of a SQL Server Instance by any means help prevent the stalling of SQL Server due to a query based on a table with millions of records. A table that will probably increase in size on a daily basis.
[font="Courier New"]-----------------------------------------------------------------------------------------------------------------------
😀 Arguing with Programmers are like wrestling a pig in mud, soon you realize the pig is enjoying it. 😀
-----------------------------------------------------------------------------------------------------------------------[/font]
February 2, 2010 at 7:43 am
roelofsleroux (2/2/2010)
Will increasing the Memory and Processor settings of a SQL Server Instance by any means help prevent the stalling of SQL Server due to a query based on a table with millions of records. A table that will probably increase in size on a daily basis.
Possibly a little, however...
Hardware upgrades only fix performance problems if the root cause of those problems is hardware bottleneck. If it is poor queries and/or poor indexes, hardware upgrades will help minimally or even not at all. In fact, I've seen a DB decrease in performance after a hardware upgrade.
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 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply