Retrieving is very slow - 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

General SQL Server Forums

New to SQL Server Programming

Retrieving is very slow

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-11-17 : 17:34:54

I got these tables
Sample_Data
-----------
SAMPLE_NUM (PK) numeric(12, 0)
SAMPLE_STATION int
SAMPLE_START_DATE datetime
SAMPLE_END_DATE datetime

Station_Data
------------
DATA_ID (PK) int
DATA_SAMPLE_NUM (FK) numeric(12, 0) Index
DATA_PARAMETER char(8) Index
DATA_METHOD char(3)
DATA_VALUE numeric(8, 4)

Station_Data has many millions of records. Sample_Data has less than 10 million.
I need to write a query to fetch unique stations from Sample_Data that has data in the Station_Data table for specific time periods and parameters.
I wrote a straight forward query like:


SELECT DISTINCT SAMPLE_STATION FROM SAMPLE_DATA
INNER JOIN STATION_DATA ON SAMPLE_NUM = DATA_SAMPLE_NUM
AND SAMPLE_START_DATE >= '1-Jan-2008' AND SAMPLE_END_DATE <= '31-Dec-2008' AND DATA_PARAMETER = 'abcd'

It takes over 30 seconds to get the data back in a Query Analyzer environment. Certainly it will take longer from a report or web page. I also tried to force using an Index like "WITH ((INDEX_NAME),NOLOCK) - but didn't help much.

Is there a way I can make it faster?
Thanks,

sqlbug

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-11-17 : 17:55:01

SELECT DISTINCT SAMPLE_STATION FROM SAMPLE_DATA
INNER JOIN STATION_DATA ON SAMPLE_DATA.SAMPLE_NUM = STATION_DATA.DATA_SAMPLE_NUM
WHERE SAMPLE_START_DATE >= '1-Jan-2008'
AND SAMPLE_END_DATE <= '31-Dec-2008'
AND DATA_PARAMETER = 'abcd'

Also create a proper index on SAMPLE_START_DATE, SAMPLE_END_DATE,DATA_PARAMETER
and include column STATION_DATA

--------------------------
http://connectsql.com/

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-11-17 : 18:31:20

Hi Lion,
I used the exact query and also have all the indexes in place. But what do you mean by "include column STATION_DATA". STATION_DATA is a table. Which column you are actually referring to?
Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-17 : 18:50:58

There is NO way we can help you, because we have absolutely no idea where the columns belong.
Please alias your tables and prefix your columns with table alias.

N 56°04'39.26"
E 12°55'05.63"

sqlbug
Posting Yak Master

201 Posts

Posted - 2011-11-18 : 11:55:10

I gave the table structures at the beginning of my post - showing all the columns involved. But if you mean I should indicate that in the query - here it is:

SELECT DISTINCT SAM.SAMPLE_STATION FROM SAMPLE_DATA SAM
INNER JOIN STATION_DATA STD ON SAM.SAMPLE_NUM = STD.DATA_SAMPLE_NUM
AND SAM.SAMPLE_START_DATE >= '1-Jan-2008' AND SAM.SAMPLE_END_DATE <= '31-Dec-2008'
AND STD.DATA_PARAMETER = 'abcd'

Also, Sample_Data is the header table with general information and Station_Data is the details table containing data for parameters.
Hope it helps.
Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources