Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Table EVENTS

TimeStamp  |O.Name   |Location  |Source     |
2008-11-12 |Pretorian|Európe    |England    |
2009-12-24 |Lex Rosa |Európe    |Italy      |
2010-01-01 |Nasdaq   |USA       |Whasington |
2010-02-12 |Plank    |Australia |Western Aus|
2010-03-11 |Pretorian|Európe    |England    |
2011-05-01 |Pretorian|Európe    |England    |

The above table is a representation of the table where different events are getting recorded. Events are created in fast time periods, so every minute there is an event created, therefore the table containing the data is really big.

I have a query to select the rows that match some kind of criteria taken from a different table and different database. This is how the 2nd table looks like.

Table TAGS

ID| Location| Source| Tagname
01| EU      | ENG   | Pretorian
02| EU      | IT    | Lex Rosa
03| USA     | WA    | Nasdaq
04| AUS     | WA    | Plank

The idea is that whenever I choose a date range e.g 2009-01-01 and 2011-06-06 I should get these rows:

2010-03-11 |Pretorian|Európe    |England    |
2011-05-01 |Pretorian|Európe    |England    |

For this I have the following query, but the execution time is really slow:

DECLARE @MaxTS Datetime 

SELECT @MaxTS = MAX(TimeStamp)
FROM LinkedServer.EVENTS
WHERE O.Name IN 
(SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS
WHERE LOCATION = 'EU' AND SOURCE = 'ENG')

IF @MaxTS > '2010-01-01'
BEGIN
  SELECT * FROM LinkedServer.EVENTS
  WHERE O.Name IN 
                 (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS
                  WHERE LOCATION = 'EU' AND SOURCE ='ENG')
END
ELSE
   PRINT 'Date out of range'

In this query I first look up the max date for the entry where location is EU and Source is ENG, and after I look up all the dates till the selected date if this is smaller than the MaxDate. Even though I get the results I am looking for, I am more curious if there is any way I could make this perform better. If you have any suggestions please let me know. Thank you!

EDIT: stored procedure used

ALTER PROCEDURE [dbo].[MyProc] 
@StartDate  NVARCHAR(19),
@EndDate    NVARCHAR(19)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @TSQL nvarchar(4000);
DECLARE @MaxDateTS nvarchar(1500);

    SELECT @MaxDateTS = MAX(TimeStamp)
    FROM LinkedServer.Events
        WHERE O.Name IN 
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS       WHERE LOCATION = 'EU' AND SOURCE = 'ENG');

IF @MaxDateTS > @StartDate
BEGIN 
    SELECT * FROM LinkedServer.Events       
               WHERE O.Name IN 
    (SELECT Tagname COLLATE DATABASE_DEFAULT FROM MyDatabase.TAGS       WHERE LOCATION = 'EU' AND SOURCE = 'ENG') AND TIMESTAMP >=  @StartDate  AND TIMESTAMP <  @EndDate
END
ELSE
PRINT 'Date out of range'

SET @TSQL = '(' + @MaxDateTS + ') ORDER BY TimeStamp';

PRINT(@TSQL);

EXEC (@TSQL);
END
share|improve this question
    
I don't see how your query is attempting to limit anything by date, except ensuring that at least one event happened after a hard-coded date of January 1st 2010. Can you explain how the query is "choosing a date range"? –  Aaron Bertrand Mar 4 '13 at 13:11
    
in this example I added a hardcoded date, but normally there is a parameter that stands for the date selected by a user. –  Greenhorn Mar 4 '13 at 13:12
1  
So show your real code - it's less confusing that way. Also is Timestamp (a terrible name for a column by the way, for multiple reasons) a datetime column or a string? Is it indexed? Why don't you have a stored procedure on the linked server instead of passing an ad hoc query there (which won't be supported by statistics in a lot of cases)? –  Aaron Bertrand Mar 4 '13 at 13:15
    
I will add the stored procedure, basically it does the same and the code is not much different just a second. –  Greenhorn Mar 4 '13 at 13:19
1  
As Aaron said about linked servers, I'm also concerned that your code does appear to be attempting to query both a linked server table and a local table at the same time in an operations similar to a join (WHERE IN) - this scenario is often a recipe for bad performance. –  Cade Roux Mar 4 '13 at 19:53
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.