Query Optimization

[benasumwa]

Hi friends!


I have a table with 12 months' payroll data for over 17,000 employees. The table has nearly 3 million rows.

The field

 has payroll items such as Basic Salary,
Bonus e.t.c. coded with numericals eg 001,100,738 and so on

The field [pfnumber] is employee's personal file no.

I have a primary key defined as follows:

[code]... ADD CONSTRAINT c_period PRIMARY KEY CLUSTERED (YEAR,MONTH,CODE,PFNUMBER)[\code]

my query is as follows:

[code]SELECT PFNUMBER,CODE,AMOUNT,BALANCE,YEAR,MONTH FROM PROCESED WHERE YEAR=2004 AND CODE=738[\code]

It takes over 10 min to process

If I modify it to include a payroll month as follows:

[code]SELECT PFNUMBER,CODE,AMOUNT,BALANCE,YEAR,MONTH FROM PROCESED WHERE YEAR=2004 AND MONTH =1 AND CODE=738[\code]

it takes 6 seconds. Pretty fast!

I need help on:


2. What I can do to speed my first query.
   (I do not need the month in it, I need records for
    the whole year)

(Please I am learning, the hard way!)

I am using MSDE 2000 on a Windows 98 box.

Thanks

Benson

 

[vongrunt]

> 2. What I can do to speed my first query.

Add dummy month interval (AND MONTH BETWEEN 1 and 12) in WHERE clause. What happens?

Note there are much more record in whole year than in single month, so don't expect 6-seconds response.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Do this, that way your index will result in a seek not a scan

SELECT PFNUMBER,CODE,AMOUNT,BALANCE,YEAR,MONTH FROM PROCESED WHERE YEAR=2004
AND MONTH BETWEEN 1 AND 12 --specify this
AND CODE=738


to look at the query plan hit CTRL + K before running the query and then look for scans (bad) index seek (good)
-------------------
best index seek
good index scan
bad table scan


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hneal98]

For one thing, you might want to put your field in the query, in the same order as your index. In other words, you query should look like this:

SELECT YEAR, MONTH, CODE, PFNUMBERE,AMOUNT,BALANCE FROM PROCESED WHERE YEAR=2004 AND CODE=738

Since you have the month in your clustered index, I would think to optomize it, you would need to include the month in your query, in the order you have the index setup.

Just my opinion.

 

[benasumwa]

Hi All

with MONTH BETWEEN 1 AND 12 added the process takes
9 minutes. Anyway, should I assume this "normal" given
that SQL goes literally through the 12 months data?

is the INDEX:

... ADD CONSTRAINT c_period PRIMARY KEY CLUSTERED (YEAR,MONTH,CODE,PFNUMBER)[\code]

properly done or should I index on single columns and have
the PK as IDENTITY(1,1) to improve perfomance?

Thanks

Benson

 

[SQLDenis]

That shouldn't take that long, I have tables with 180 million rows and I never see responses over 1 minute, returning 100000 rows takes me about 15 seconds

now let's see, what about combining year and month into YearMonth (saving 4 bytes per row)

also what is your fragmentation, run DBCC SHOWCONTIG
When was the last time you updated statiscics?
How wide are your rows (The wider the rows the more IO is required to return data), maybe time to normalize a bit





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

What happens if cluster completely covers query (removed AMOUNT and BALANCE columns):

SELECT PFNUMBER,CODE, YEAR,MONTH FROM PROCESED WHERE YEAR=2004
AND MONTH BETWEEN 1 AND 12
AND CODE=738

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[benasumwa]

Denis,

My box runs Windows 98 with 56MB of RAM! What do you
think?

now let's see, what about combining year and month into YearMonth (saving 4 bytes per row)[\quote]

I will be doing this, I thought so.

When was the last time you updated statiscics?[\quote]

I updated statitics 3 days ago.

How wide are your rows[\quote]

16 columns mostly "numeric" type

? memory

Benson

 

[SQLDenis]

My box runs Windows 98 with 56MB of RAM! What do you
think?)

So you don't really have a problem, it's the lack of memory, I am surprised you get a result at all instead of "some rows have been dropped because resources are running low"

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Do you have an index on the code field?

If getting the data for one month takes six seconds, you could try pulling the data for each month separately into a temp table and then selecting from the temp table.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[benasumwa]

SQLSister

I do not have an index on

 field. In fact I asked whether it will be okay to index on all columns
I use on the WHERE clause. Is it wise?


Benson

 

[SQLDenis]

your WHERE clause should be covered by the index or you might get table scans (very bad)

AFAIK Code is part of your primary key (clustered index)

ADD CONSTRAINT c_period PRIMARY KEY CLUSTERED (YEAR,MONTH,CODE,PFNUMBER)

anyway did you look at the execution plan and what do you get scans or seeks

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[benasumwa]

SQLDenis,

With MSDE 2000 (Free Tool) I guess I should
be able to get the execution plan.

True? Just learning!

Benson