November 22, 2011 at 3:28 am
Hi
I have a query that looks like this
declare @temp table(Lat smallint, Long smallint, vesselCount int)
INSERT INTO @temp
SELECT Latitude, Longitude, dbo.GetZoneBlockCount(Latitude, Longitude)
FROM ZoneBlocks WITH (NOLOCK)
UPDATE ZoneBlocks
SET VesselCount = (select VesselCount from @temp where Lat = Latitude and long =Longitude)
when it runs it takes long, I know this way is far from the right way to do what it's doing but I just can't figure out how to do it right.
Somebody please help. thanks in advance 🙂
The Fastest Methods aren't always the Quickest Methods
November 22, 2011 at 3:41 am
BinaryDigit (11/22/2011)
HiI have a query that looks like this
declare @temp table(Lat smallint, Long smallint, vesselCount int)INSERT INTO @temp
SELECT Latitude, Longitude, dbo.GetZoneBlockCount(Latitude, Longitude)
FROM ZoneBlocks WITH (NOLOCK)
UPDATE ZoneBlocks
SET VesselCount = (select VesselCount from @temp where Lat = Latitude and long =Longitude)
when it runs it takes long, I know this way is far from the right way to do what it's doing but I just can't figure out how to do it right.
Somebody please help. thanks in advance 🙂
Hello and welcome to SSC!
The main reason that you are not getting any replies for your question, it that we don't have enough information from you to answer!
For starters, it seems that your DDL script has fallen off your post as has your readily consumable sample data. Or perhaps you were unaware of the benefits of providing these things?
When you have time, please read this article[/url] about the best way to provide us with working sample data and DDL scripts. Be sure to include DDL for your table ZoneBlocks and your function GetZoneBlockCount. This will allow the unpaid volunteers of this site to provide you with working, tested code for your particular problem.
Also, as this is a performance issue, you could do with reading through this article[/url] in addition to the previous one I mentioned. This will explain all of the information that we need from you to help tune your query.
My initial thoughts are that inserting everything into a table variable then updating from that instead of just updating your table is causing massive extra IO. Also, use of NOLOCK is not a "go faster" switch. It means that you have the strong possibility of getting dirty reads, which means you could be updating your table with incorrect data. For more information, make sure you read through the two articles I mentioned: -
(*)http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
(*)http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
Thanks.
November 22, 2011 at 3:46 am
Thanks, will read those links now.
The Fastest Methods aren't always the Quickest Methods
November 22, 2011 at 4:16 am
Why don't you just directly perform the update ?
What's inside the function dbo.GetZoneBlockCount ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 22, 2011 at 4:28 am
Hi
I think I have put everything that could help somebody answer my problem in a zip file. Please let me know if there is anything else that can help with my problem.
AISPositionsCurrent has 98520 rows in it in developer database and 236491 in the production db. This table also has about 1500 or more updates/inserts combined every second.
Trying to get an execution plan now for the query in the main post from production now, have included from dev in the current zip file. Get a deadlock error when running in production, so have included estimated execution plan
The Fastest Methods aren't always the Quickest Methods
November 22, 2011 at 4:35 am
Yeah, no-one is going to download a zip file on a programming website 😉
Attach them as text files
November 22, 2011 at 4:51 am
Hi
I have placed the individual files, only put the zip becasuse the performance article said to do so 😀
The Fastest Methods aren't always the Quickest Methods
November 22, 2011 at 5:42 am
Can you include sample data for the [AISPositionsCurrent] table ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 22, 2011 at 5:44 am
I will try get that in now.
Sorry I tried to generate sample data as shown in the articles I read like this
SELECT 'SELECT '
+ QUOTENAME(AISNavigationStatusID,'''')+','
+ QUOTENAME(MMSI,'''')+','
+ QUOTENAME(Speed,'''')+','
+ QUOTENAME(Position,'''')+','
+ QUOTENAME(PositionLat,'''')+','
+ QUOTENAME(PositionLong,'''')+','
+ QUOTENAME(Course,'''')+','
+ QUOTENAME(TrueHeading,'''')+','
+ QUOTENAME(RateOfTurn,'''')+','
+ QUOTENAME(Bearing,'''')+','
+ QUOTENAME(PositionAccuracy,'''')+','
+ QUOTENAME(ReceivedUTC,'''')+','
+ QUOTENAME(OriginalReceivedUTC,'''')
+ ' UNION ALL'
FROM AISPositionsCurrent
but I get the error
Msg 260, Level 16, State 3, Line 1
Disallowed implicit conversion from data type geography to data type nvarchar, table 'AISPositionsCurrent', column 'Position'. Use the CONVERT function to run this query.
anybody know how to do this convert 🙁
The Fastest Methods aren't always the Quickest Methods
November 22, 2011 at 5:54 am
If you can try to avoid the scalar value function ...
this may be worth a shot :
UPDATE Z
SET VesselCount = C.vesselCount
from ZoneBlocks Z
INNER JOIN (
SELECT Z.Latitude
, Z.Longitude
, count(*) as vesselCount
FROM ZoneBlocks Z
inner join aispositionscurrent P
on P.positionlat between ( Z.Latitude - 3 ) and Z.Latitude
and P.positionlong between Z.Longitude
and ( Z.Longitude + 6 )
and P.ReceivedUTC > dateadd(ss, -14400, GETUTCDATE()) -- = and dbo.DateOlderThanXHoursUTC(aispositionscurrent.ReceivedUTC) = 0
group by Z.Latitude
, Z.Longitude
) C
on C.Latitude = Z.Latitude
and C.Longitude = Z.Longitude ;
btw Chances are changing your scalar valued function a an Inline Table Valued function will already enhance your performance quite a lot.
Keep in mind you'll have to modify your queries to use outer/cross apply for it
CREATE FUNCTION [dbo].[ITVF_GetZoneBlockCount]
( @posLat float
, @posLong float
)
RETURNS table
AS return (
select count(*) ans
from aispositionscurrent
where positionlat between ( @posLat - 3 ) and @posLat
and positionlong between @posLong and ( @posLong + 6 )
and aispositionscurrent.ReceivedUTC > dateadd(ss, -14400, GETUTCDATE()) -- = and dbo.DateOlderThanXHoursUTC(aispositionscurrent.ReceivedUTC) = 0
)
;
btw [DateOlderThanXHoursUTC] is also a scalar value function that will also have your system gain a bunch converting it to a ITVF.
SQLEngine is designed to handle sets, even if it's empty or only holds one row, it will perform better than handling non-set items.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 22, 2011 at 6:23 am
Thanks, this runs a lot faster, I will do more research into what you have mention to fully take advantage of the way this query was done. 😀
The Fastest Methods aren't always the Quickest Methods
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply