Profiler results - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Profiler results

Author

Topic

nealpawlowski
Starting Member

3 Posts

Posted - 2003-05-17 : 09:06:52

I have a table named provagre. Here is the tables' structure:

CREATE TABLE [dbo].[PROVAGRE] (
[AGRE_ID] [int] NOT NULL ,
[PROV_ID] [int] NOT NULL ,
[AGRE_TYPE] [int] NOT NULL ,
[AGRE_DATE] [datetime] NULL ,
[EFF_DATE] [datetime] NULL ,
[EXP_DATE] [datetime] NULL ,
[AGRE_1BY] [int] NOT NULL ,
[AGRE_2BY] [int] NOT NULL ,
[SIGN_BY] [int] NOT NULL ,
[BILL_TERMS] [int] NOT NULL ,
[PAY_TERMS] [int] NOT NULL ,
[AGRE_NOTE] [text] NULL ,
[TOUCH_DATE] [datetime] NULL ,
[TOUCH_BY] [int] NOT NULL ,
[INSU_ID] [int] NOT NULL ,
[SUBCAP] [float] NOT NULL ,
[WITH_PERC] [numeric](6, 2) NULL ,
[CHK_SUB] [tinyint] NOT NULL ,
[PARENT_ID] [int] NOT NULL ,
[PRAGR_DEF] [tinyint] NOT NULL ,
[PRAGR_AUTH] [tinyint] NOT NULL ,
[PRAGR_APPR] [tinyint] NOT NULL ,
[PRAGR_TRK] [tinyint] NOT NULL ,
[PRAGR_PD] [tinyint] NOT NULL ,
[PRAGR_RSN] [int] NOT NULL ,
[AGRAMT] [money] NULL ,
[UNLIMAMT] [tinyint] NOT NULL ,
[AUTHAMT] [money] NULL ,
[APPRAMT] [money] NULL ,
[PAIDAMT] [money] NULL ,
[CHK_WHLD] [tinyint] NOT NULL ,
[WHLD_PERC] [numeric](6, 2) NOT NULL ,
[FORFEITCODE_ID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE INDEX [PROVAGRE_PROV_ID] ON [dbo].[PROVAGRE]([PROV_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PROVAGRE_AGRE_TYPE] ON [dbo].[PROVAGRE]([AGRE_TYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

In the application, I've captured these two SELECT statements using Profiler:

select * from provagre where prov_id = 4810 and (('04/29/2003' between eff_date and exp_date) or ('04/29/2003' >= eff_date and exp_date is null) or ('04/30/2003' between eff_date and exp_date )or ('04/30/2003' >= eff_date and exp_date is null)) and agre_id in (select d.agre_id from service a, servcomp b, servcont c, servagre d where c.site_id = 11076 and b.loc_id = 266 and a.serv_id = b.serv_id and a.serv_id = c.serv_id and a.serv_id = d.serv_id)

select provagre.* from provagre

I get high reads, CPU and duration with these two statements. Do you think indexing would help, specifically the agre_ID? Perhaps a clustered PK there? Please help!

Neal

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 10:06:32

See if this makes a difference.
Also can you post ddl for the following
service
servcomp
servcont
servagre

and some sample data

Also is it possible that eff_date and exp_date be the same date?
If not then you can optimize the date portion of the where to something like
(p.eff_date <= '04/29/2003' AND (p.exp_date >= '04/30/2003' OR p.exp_date IS NULL)

or make it a variable that you pass in to a stored proc.
(p.eff_date <= @startdate AND (p.exp_date >= @enddate OR p.exp_date IS NULL)

select *
from provagre
where
prov_id = 4810 and
(eff_date <= '04/29/2003' and (exp_date >= '04/29/2003' or exp_date is null) or
eff_date <= '04/30/2003' and (exp_date >= '04/30/2003' or exp_date is null)) and
agre_id in (
select d.agre_id
from
service a
LEFT JOIN servcomp b ON a.serv_id = b.serv_id
LEFT JOIN servcont c ON b.serv_id = c.serv_id
LEFT JOIN servagre d ON c.serv_id = d.serv_id
WHERE
b.loc_id = 266 AND
c.site_id = 11076 AND
)

also try

SELECT p.*
FROM
provagre p
INNER JOIN servagre d ON p.agre_id = d.agred_id
INNER JOIN servcont c ON c.serv_id = d.serv_id
INNER JOIN servcomp b ON b.serv_id = c.serv_id
WHERE
p.prov_id = 4810 and
b.loc_id = 266 AND
c.site_id = 11076 AND
(p.eff_date <= '04/29/2003' AND (p.exp_date >= '04/29/2003' OR p.exp_date IS NULL) OR
p.eff_date <= '04/30/2003' AND (p.exp_date >= '04/30/2003' OR p.exp_date IS NULL))

Edited by - ValterBorges on 05/17/2003 10:19:36

nealpawlowski
Starting Member

3 Posts

Posted - 2003-05-17 : 10:23:35

quote:

See if this makes a difference.
Also can you post ddl for the following
service
servcomp
servcont
servagre

and some sample data

Also is it possible that eff_date and exp_date be the same date?
If not then you can optimize the date portion of the where to something like
(p.eff_date <= '04/29/2003' AND (p.exp_date >= '04/30/2003' OR p.exp_date IS NULL)

or make it a variable that you pass in to a stored proc.
(p.eff_date <= @startdate AND (p.exp_date >= @enddate OR p.exp_date IS NULL)

select *
from provagre
where
prov_id = 4810 and
(eff_date <= '04/29/2003' and (exp_date >= '04/29/2003' or exp_date is null) or
eff_date <= '04/30/2003' and (exp_date >= '04/30/2003' or exp_date is null)) and
agre_id in (
select d.agre_id
from
service a
LEFT JOIN servcomp b ON a.serv_id = b.serv_id
LEFT JOIN servcont c ON b.serv_id = c.serv_id
LEFT JOIN servagre d ON c.serv_id = d.serv_id
WHERE
b.loc_id = 266 AND
c.site_id = 11076 AND
)

also try

SELECT p.*
FROM
provagre p
INNER JOIN servagre d ON p.agre_id = d.agred_id
INNER JOIN servcont c ON c.serv_id = d.serv_id
INNER JOIN servcomp b ON b.serv_id = c.serv_id
WHERE
p.prov_id = 4810 and
b.loc_id = 266 AND
c.site_id = 11076 AND
(p.eff_date <= '04/29/2003' AND (p.exp_date >= '04/29/2003' OR p.exp_date IS NULL) OR
p.eff_date <= '04/30/2003' AND (p.exp_date >= '04/30/2003' OR p.exp_date IS NULL))

Edited by - ValterBorges on 05/17/2003 10:19:36

The app is using the SELECTs as shown in the trace using Profiler. I thought about using JOINs but do not know how to get the app to do that?????

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 10:29:04

what app?
who wrote it?
what does the query feed? asp, report, form?????
what is it's use?

nealpawlowski
Starting Member

3 Posts

Posted - 2003-05-17 : 11:51:05

quote:

what app?
who wrote it?
what does the query feed? asp, report, form?????
what is it's use?

The company I'm consulting for wrote the app. Issue is, the SELECT appears in the SQL trace via Profiler. On the front end, the app is being used going through various windows while querying SQL on the backend. I see the SELECT it uses but wonder how to get it to do the JOINs instead. Probably a programming thing.....

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-17 : 11:53:20

Need to change the code in the app.

This is why people who write apps should consult with a dba and have their sql stored in views, sp's and udf's so when changes need to be made you don't need a patch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources