Why is my Sproc sooo slooow .. and how will it be faster!
-
Hi everybody
I have create a Sproc which will return Date, time and some other values of a last inserted record.
It works, but it takes more then a minute before the records are returned My code is probably not very efficient, but I am stuck of how to do this in a better (faster) way.
Please help ...
Ronda
Here's the procedure:
ALTER PROCEDURE [dbo].[last_id_each_device_2]
AS
SELECT 'Device 55: Last Person detected at ' + CAST(dbo.view_person_count_events_dvc55.weekday AS VARCHAR)
+ ' ' + CAST(dbo.view_person_count_events_dvc55.[date] AS VARCHAR) + ' ' + CAST(dbo.view_person_count_events_dvc55.[month] AS VARCHAR)
+ ' ' + CAST(dbo.view_person_count_events_dvc55.[year] AS VARCHAR) + ', ' + CAST(dbo.view_person_count_events_dvc55.[time] AS VARCHAR)
AS last_event_dvc55,
'The local temperature is about ' + dbo.view_person_count_substr_attrib_2.LOCAL_TEMP_DC + ' °C, and the potmeter is set to ' + dbo.view_person_count_substr_attrib_2.POTMETER_SETTING
+ ' Ohm.' AS param_values_dvc55
FROM dbo.view_person_count_events_dvc55 INNER JOIN
dbo.view_person_count_substr_attrib_2 ON dbo.view_person_count_events_dvc55.objectid = dbo.view_person_count_substr_attrib_2.objectid
… Another four, similar Select statements are Union-ed here (One for each device)
GO
The FROM part of the above code refers to this View:
ALTER VIEW dbo.view_person_count_events_dvc55
AS
SELECT TOP 1 DATENAME(WEEKDAY, [timestamp]) AS weekday, DATEPART(DD, [timestamp]) AS [date], DATENAME(MONTH, [timestamp]) AS [month],
DATENAME(YEAR, [timestamp]) AS [year], CONVERT(CHAR(15), [timestamp], 108) AS [time], objectid
FROM dbo.eventlog
WHERE (RIGHT(attributes, 4) LIKE '%55}')
GROUP BY attributes, [timestamp], objectid
ORDER BY [timestamp] DESC
GO
-
first of all :
- ... FROM dbo.eventlog
WHERE (RIGHT(attributes, 4) LIKE '%55}')
will cause a table-scan ! because with the function no index for "attributes" can be used.
Maybe a Reversed_attributes char(4) extra column with an index and queried with like '}55%' would be a better way for the optimizer to peep into the data, altough AFAIK starting a like with a wildcard discourages index usage.
- dbo.view_person_count_substr_attrib_2 is not documented in your thread
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
-
Hi, thanks for Your reply.
View dbo.view_person_count_substr_attrib_2 was not documented.
It also relies on another underlying source.
The whole setup works but it’s very slow and not efficient code.
If you have any suggestions pls. let me know.
Thanks, Ronda
Here’s the sequence:
Table: dbo.eventlog
View: dbo.view_person_count_substr_attrib_1
Search for a pattern in one big text string with relevant event data, and placed commas.
ALTER VIEW dbo.view_person_count_substr_attrib_1
AS
SELECT TOP 1 PERCENT objectid, REPLACE(SUBSTRING(attributes, LEN(attributes) - CHARINDEX(REVERSE('{'), REVERSE(attributes)) + 2, LEN(attributes)
- 1 - LEN(attributes) + CHARINDEX(REVERSE('{'), REVERSE(attributes)) - 1), '0x', '') AS sub_attributes
FROM dbo.eventlog
ORDER BY objectid DESC
GO
View: dbo.view_person_count_substr_attrib_2
Convert the Comma Separated Values of dbo.view_person_count_substr_attrib_1 to several substrings.
ALTER VIEW dbo. view_person_count_substr_attrib_2
AS
SELECT TOP 100 PERCENT objectid, SUBSTRING(sub_attributes + ',', 0 + 1, CHARINDEX(',', sub_attributes + ',', 0 + 1) - 0 - 1) AS COLUMN_1,
SUBSTRING(sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1, CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1)
- CHARINDEX(',', sub_attributes + ',') - 1) AS COLUMN_2, SUBSTRING(sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',') + 1) + 1, CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1)
- CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) - 1) AS COLUMN_3, SUBSTRING(sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1, CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) - CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) - 1) AS POTMETER_SETTING,
SUBSTRING(sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1, CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1) - CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) - 1) AS LOCAL_TEMP_DC,
SUBSTRING(sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1) + 1, CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',') + 1) + 1) + 1) + 1) + 1) - CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1) - 1) AS COLUMN_6, SUBSTRING(sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',',
CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1) + 1) + 1, CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',') + 1) + 1) + 1) + 1) + 1) + 1) - CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',', sub_attributes + ',', CHARINDEX(',',
sub_attributes + ',') + 1) + 1) + 1) + 1) + 1) - 1) AS DEVICE_SERIALNR_1
FROM dbo.view_person_count_substr_attrib_1
ORDER BY objectid DESC
GO
-
- Can you try to execute the queries without the TOP-keyword (and order by) in the views ?
- Like you can see, maybe you would be better off by altering your table eventlog so it contains normalized data in stead of a string column that contains all the data. Maybe even have it split into a # of eventlog-tables. Overhere we have had that issue when converting IMS to db2, back in the days. The migration was quick (and dirty) but the pain can still be enjoyed on those subsystems that have not been reworked yet.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply