optimization of select - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

optimization of select

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-22 : 08:33:21

Hi,
Is it possible to optimize the follwoing queries?
As you can see, I am creating table variables to join them together and end up with one table at the end.
If you think there is a way to alter the following queries to increase the performance, then please let me know.
Many thanks

--BID data...
declare @tblPricesFinalBID table
(
Security_ID smallint,
Security_Name varchar(50),
Bid_Source varchar(1000),
Bid_Size_Best_Latest decimal(12, 2),
Bid_Price_Best_Latest decimal(12, 4),
Bid_Price_Best_AllDay decimal(12, 4),
Ask_Price_Best_AllDay decimal(12, 4)
)

insert into
@tblPricesFinalBID

SELECT
Security_ID, Security_Name, Source_Code, Bid_Size, Bid_Price, Bid_Price_Best_AllDay, Ask_Price_Best_AllDay
FROM
(
SELECT
t.Security_ID,
t.Security_Name,
Source_Code =
case
when t.Bid_Price_Best_Latest is null then isnull(dbo.fnSourceShortCode(s.Source_Code),'') + ' - ' + convert(varchar(20), h.Import_Date, 103) + ' ' + convert(varchar(5), h.Import_Date, 108)
else isnull(dbo.fnSourceShortCode(s.Source_Code),'')
end,
Bid_Size =
case
when charindex('A', ltrim(rtrim(h.Bid_Size))) > 0 then replace(h.Bid_Size, 'A', '')
when charindex('E', ltrim(rtrim(h.Bid_Size))) > 0 then replace(h.Bid_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(h.Bid_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Bid_Size)))
end,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Bid_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Bid_Price)))
end as Bid_Price,
t.Bid_Price_Best_AllDay,
NULL as Ask_Price_Best_AllDay,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
FROM
@tblPricesFinal t left join tblSources s on t.Security_ID = s.Security_ID inner join tblPricesSourcesImportHistory h on
s.Source_ID = h.Source_ID
WHERE
ltrim(rtrim(len(h.Bid_Price))) > 1
and t.Bid_Price_Best_Latest is null
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Bid_Price))) > 1

---------------------------

--Ask data...
declare @tblPricesFinalASK table
(
Security_ID smallint,
Security_Name varchar(50),
Ask_Source varchar(1000),
Ask_Size_Best_Latest decimal(12, 2),
Ask_Price_Best_Latest decimal(12, 4),
Bid_Price_Best_AllDay decimal(12, 4),
Ask_Price_Best_AllDay decimal(12, 4)
)

insert into
@tblPricesFinalASK

SELECT
Security_ID, Security_Name, Source_Code, Ask_Size, Ask_Price, Bid_Price_Best_AllDay, Ask_Price_Best_AllDay
FROM
(
SELECT
t.Security_ID,
t.Security_Name,
Source_Code =
case
when t.Ask_Price_Best_Latest is null then isnull(dbo.fnSourceShortCode(s.Source_Code),'') + ' - ' + convert(varchar(20), h.Import_Date, 103) + ' ' + convert(varchar(5), h.Import_Date, 108)
else isnull(dbo.fnSourceShortCode(s.Source_Code),'')
end,
Ask_Size =
case
when charindex('A', ltrim(rtrim(h.Ask_Size))) > 0 then replace(h.Ask_Size, 'A', '')
when charindex('E', ltrim(rtrim(h.Ask_Size))) > 0 then replace(h.Ask_Size, 'E', '')
when dbo.isreallynumeric(ltrim(rtrim(h.Ask_Size))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Ask_Size)))
end,
case
when dbo.isreallynumeric(ltrim(rtrim(h.Ask_Price))) = 1 then convert(decimal(12, 8), ltrim(rtrim(h.Ask_Price)))
end as Ask_Price,
NULL as Bid_Price_Best_AllDay,
t.Ask_Price_Best_AllDay,
ROW_NUMBER() OVER (PARTITION BY t.Security_ID ORDER BY Import_Date DESC) AS RecID
FROM
@tblPricesFinal t left join tblSources s on t.Security_ID = s.Security_ID inner join tblPricesSourcesImportHistory h on
s.Source_ID = h.Source_ID
WHERE
ltrim(rtrim(len(h.Ask_Price))) > 1
--and t.Ask_Price_Best_Latest is null
) AS d
WHERE
RecID = 1
and ltrim(rtrim(len(Ask_Price))) > 1

----------------------

update
pf
set
pf.Bid_Source = b.Bid_Source,
pf.Bid_Size_Best_Latest = b.Bid_Size_Best_Latest,
pf.Bid_Price_Best_Latest = b.Bid_Price_Best_Latest
from
@tblPricesFinal pf
inner join @tblPricesFinalBID b on pf.Security_ID = b.Security_ID
----------------------
update
pf
set
pf.Ask_Source = a.Ask_Source,
pf.Ask_Size_Best_Latest = a.Ask_Size_Best_Latest,
pf.Ask_Price_Best_Latest = a.Ask_Price_Best_Latest
from
@tblPricesFinal pf
inner join @tblPricesFinalASK a on pf.Security_ID = a.Security_ID
----------------------
select
Security_ID,
Security_Name,
Bid_Source,
Bid_Size_Best_Latest,
Bid_Price_Best_Latest,
Ask_Price_Best_Latest,
Ask_Size_Best_Latest,
Ask_Source,
Bid_Price_Best_AllDay,
Ask_Price_Best_AllDay
from
@tblPricesFinal
order by
Security_Name

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-22 : 18:56:36

It appears from the predicate ltrim(rtrim(len(Bid_Price))) > 1 that you always need a full table scan to get the data. As long as your joins are indexed that is the best you can hope for with this predicate.
However bid & ask prices appear to be strings rather than numbers. Fix that up, get rid of all the trimming crap and you might (just might) benefit from an index on price or a composite on security_ID and price. I presume all your primary and foreign keys are indexed. If not, do that.

Personally I would not use a temp table for this. Do a union and join back straight from that for your update. You can certainly do a single update not one for ask one for bid.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-23 : 11:10:20

Hi,
Added the mentioned indexes.
I can see there is a performance gain by adding indexes to prices. thank you

1) Not sure why adding index to a price field with type decimal(12, 8) increases the speed. I always thought index benefits on text and date field.

2) Based on what you said as follows:
"Personally I would not use a temp table for this. Do a union and join back straight from that for your update. You can certainly do a single update not one for ask one for bid."

Will this alone have any improvement in speed?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-23 : 18:45:11

quote:
Originally posted by arkiboys
Will this alone have any improvement in speed?

Don't know but I would expect it to. No temp table to create, therefore no write IO or joins to the unindexed table. A single query requires fewer resources than multiples in terms of parsing and possibly execution IO (especially in this case where it will essentially read the same data twice).

I haven't looked in detail, but it looks possible to do this with the same query and not even do the union thing.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-01-25 : 06:18:07

I will try this and come back to you.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources