May 16, 2008 at 3:58 am
Hi,
I'm new with stored procedures and I don't know if what I want to do is possible.
I have in my application a dynamic query that can be spread over 3 tables (linked with joins).
But first the database structure witch cannot be changed (external systems):
Table Bernt (193 columns):
FLTIME datetime, indexed
Meting1 float Meting (in Dutch) = Measurement
Meting2 float
...
Meting192 float
TeOud boolean (is set to true by a insert trigger if FLTIME is older than 7 days)
Table Draeger (165 columns):
FLTIME datetime, indexed
Meting1 float
Meting2 float
...
Meting64 float
TeOud boolean
Table MDA (129 columns):
FLTIME datetime, indexed
Meting float
Meting2 float
...
Meting128 float
TeOud boolean
Table Detector: Groupnumber int
State nvarchar(10)
TAG nvarchar(50) [used for linking to the three other tables]
Loglevel float
... other fields witch we don't need
In the first three tables there is a new record added each second. So the amount of data is huge.
The SQL Server is set up to make a backup and I restore it programmatically to a other database so we don't have any problem with insert locks.
Now I have to run some queries, depending on the state of a measurement (Active/inactive) and the groupnumber (defined in table Detector).
I do this in my VB.NET2005 program with ADO.NET. But this is to slow and doesn't work all the time (Connection time out).
So maybe it's possible to put in into a stored procedure to speed up the process.
The query is variable as I already told you:
Query = SELECT B.FLTIME, B.Meting103 AS B_Meting103, B.Meting104 AS B_Meting104, B.Meting105 AS B_Meting105, B.Meting106 AS B_Meting106,
D.Meting2 AS D_Meting2 FROM Bernt B, Draeger D WHERE ( B.Meting103 > 0.7 OR B.Meting104 > 10.8 OR B.Meting105 > 0.5 OR B.Meting106 > 0.8
OR D.Meting2 > 3.8) AND B.TEOUD = 1 AND B.FLTIME = D.FLTIME ORDER BY B.FLTIME ASC
Query = SELECT B.FLTIME, B.Meting42 AS B_Meting42, B.Meting43 AS B_Meting43 FROM Bernt B WHERE ( B.Meting42 > 6.0 OR B.Meting43 > 0.8) AND B.TEOUD = 1 ORDER BY B.FLTIME ASC
Largest possible query = SELECT B.FLTIME, B.Meting1 AS B_Meting1, B.Meting2 AS B_Meting2, ..., B.Meting192 AS B_Meting192, D.Meting1 AS D_Meting1, ...,
D.Meting64 AS D_Meting64, M.Meting1 AS M_Meting1, ..., M.Meting128 AS M_Meting128 FROM Bernt B, Draeger D, MDA M WHERE (B.Meting1 > 0.5 OR B.Meting1 > 0.8
OR ... [for each measurement]) AND B.TEOUD = 1 AND B.FLTIME = D.FLTIME AND B.FLTIME = M.FLTIME ORDER BY B.FLTIME ASC
I tried with this query but that didn't work:
ALTER PROCEDURE [dbo].[usp_GetBrentDraegerMdaData]
@Columnnames ntext,
@Tablenames nvarchar(100),
@WherePart ntext
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 10 @Columnnames FROM @Tablenames WHERE Bernt.FLTIME < DATEDIFF(d,3, getdate()) and @WherePart
END And also this:
ALTER PROCEDURE [dbo].[usp_GetBrentDraegerMdaData]
-- Add the parameters for the stored procedure here
@Tabellen nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
SET @Commandstring = 'SELECT top 10 Meting1 FROM ' + @Tabellen
EXEC @CommandString
END
If I replace every parameter to the text it should be then I works fine. But the text must be a parameter.
The 'TOP 10' is just for limiting the result for testing, otherwise the resultcount exceeds 25*10^6 records.
Does anyone have a solution for this problem or any other thing that I could try for getting the data?
May 16, 2008 at 4:51 am
Hi,
Your first impression was, ok. However, this is the correct procedure:
ALTER PROCEDURE [dbo].[usp_GetBrentDraegerMdaData]
@Columnnames nvarchar(max),
@Tablenames nvarchar(max),
@WherePart nvarchar(max),
@debug bit = 0
AS
BEGIN
SET NOCOUNT ON;
declare @SQLCmd nvarchar(max)
set @SQLCmd = 'SELECT TOP 10 ' + @Columnnames + ' FROM ' + @Tablenames + ' WHERE ' + @WherePart
if @debug = 0
exec (@SQLCmd)
else
print @SQLCmd
END
go
Example:
exec usp_GetBrentDraegerMdaData @columnnames = 'B.FLTIME, B.Meting42 AS B_Meting42, B.Meting43 AS B_Meting43'
, @tablenames = 'Bernt B'
, @wherePart = '( B.Meting42 > 6.0 OR B.Meting43 > 0.8) AND B.TEOUD = 1'
I assume you're using SQL2005, otherwise change nvarchar(max) to nvarchar(8000)
@debug is just a parameter I (always) add to test the stored procedure.
Final question: Has your application something to do with firefighter equipment? the names sound very familiar to me 🙂
Greetings from a dutch DBA (and voluntary firefighter)
Wilfred
The best things in life are the simple things
May 16, 2008 at 5:26 am
Hi Wilfred,
This is application is not for firefighters :).
It's used to measure the toxic level of gasses in a factory who creates microchips and semiconductors.
My program needs to export the warning and alarm values to a CSV-file and then archive the database.
I tried your reply code and that works.
Now I going to stress test it on a database with about 500000 records.
Thx for the help Wilfred and keep up the firefighting ;).
Greetings (from a Dutch Software Development Engineer)
May 16, 2008 at 6:30 am
You should look into the BOL and check out how to do ANSI standard joins. It'll make your code more readable and can improve performance by limiting data at the initial join instead of joining everything to everything and then filtering the results. Also, now that you have the query working, take a look at the execution plan to ensure it's using the indexes on your tables (you have those, right, especially clustered indexes).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply