|
Home > Archive > microsoft.public.sqlserver.server > June 2002 > Re: sql server 2000 stored procedure very slow performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: sql server 2000 stored procedure very slow performance
|
|
| Anthony Altieri 2002-06-28, 7:25 am |
| I believe this is the information you requested.
I am sorry i misspelled performance my first post and screwed that up.
Still need help
CREATE TABLE [dbo].[tblpolcoverage] (
[IDX] [int] IDENTITY (182363, 1) NOT NULL ,
[MASTERIDX] [int] NOT NULL ,
[AG_NO] [int] NOT NULL CONSTRAINT [DF_tblpolcoverage_AG_NO] DEFAULT
(0),
[PR_NO] [int] NOT NULL CONSTRAINT [DF_tblpolcoverage_PR_NO] DEFAULT
(0),
[TRANS_DT] [smalldatetime] NOT NULL ,
[EFF_DT] [smalldatetime] NULL ,
[EXP_DT] [smalldatetime] NULL ,
[POL_NUM] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PREMIUM] [smallmoney] NOT NULL CONSTRAINT
[DF_tblpolcoverage_PREMIUM] DEFAULT (0),
[TAX] [smallmoney] NOT NULL CONSTRAINT [DF_tblpolcoverage_TAX]
DEFAULT (0),
[POLFEE] [smallmoney] NOT NULL CONSTRAINT [DF_tblpolcoverage_POLFEE]
DEFAULT (0),
[DRAFTAMT] [smallmoney] NOT NULL CONSTRAINT
[DF_tblpolcoverage_DRAFTAMT] DEFAULT (0),
[BALANCE] [smallmoney] NOT NULL CONSTRAINT
[DF_tblpolcoverage_BALANCE] DEFAULT (0),
[COV_AMT] [smallmoney] NOT NULL CONSTRAINT
[DF_tblpolcoverage_COV_AMT] DEFAULT (0),
CONSTRAINT [PK_tblpolcoverage] PRIMARY KEY NONCLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IX_tblpolcoverageTRANS_DT] ON
[dbo].[tblpolcoverage]([TRANS_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoverageAG_NO] ON
[dbo].[tblpolcoverage]([AG_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoveragePR_NO] ON
[dbo].[tblpolcoverage]([PR_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoverageMASTERIDX] ON
[dbo].[tblpolcoverage]([MASTERIDX]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoverageDRAFTAMT] ON
[dbo].[tblpolcoverage]([DRAFTAMT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoverageEFF_DT] ON
[dbo].[tblpolcoverage]([EFF_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolcoverageEXP_DT] ON
[dbo].[tblpolcoverage]([EXP_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TABLE [dbo].[tblpolmaster] (
[IDX] [int] IDENTITY (1, 1) NOT NULL ,
[AG_NO] [int] NOT NULL CONSTRAINT [DF_tblpolmaster_AG_NO] DEFAULT
(0),
[PR_NO] [int] NOT NULL CONSTRAINT [DF_tblpolmaster_PR_NO] DEFAULT
(0),
[PLAN_NO] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FIN_NO] [int] NOT NULL CONSTRAINT [DF_tblpolmaster_FIN_NO] DEFAULT
(0),
[ACCT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DRAFT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_LNAME] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_FNAME] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_ADDRESS] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_CITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_ZIP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_PLUS4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_FAX] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_MEMBER] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POL_NUM] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PR_COUNT] [int] NOT NULL CONSTRAINT [DF_tblpolmaster_PR_COUNT]
DEFAULT (0),
[TRANS_DT] [smalldatetime] NULL ,
[PR_CERT] [int] NOT NULL CONSTRAINT [DF_tblpolmaster_PR_CERT] DEFAULT
(0),
CONSTRAINT [PK_tblpolmaster] PRIMARY KEY CLUSTERED
(
[IDX]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPLAN_NO] ON
[dbo].[tblpolmaster]([PLAN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_NAME] ON
[dbo].[tblpolmaster]([POL_LNAME], [POL_FNAME]) WITH FILLFACTOR = 90
ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_STATE] ON
[dbo].[tblpolmaster]([POL_STATE]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_NUM] ON
[dbo].[tblpolmaster]([POL_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterAG_NO] ON [dbo].[tblpolmaster]([AG_NO])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPR_NO] ON [dbo].[tblpolmaster]([PR_NO])
WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_ADDRESS] ON
[dbo].[tblpolmaster]([POL_ADDRESS]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_PHONE] ON
[dbo].[tblpolmaster]([POL_PHONE]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPR_COUNT] ON
[dbo].[tblpolmaster]([PR_COUNT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterFIN_NO] ON
[dbo].[tblpolmaster]([FIN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterUSER_ID] ON
[dbo].[tblpolmaster]([USER_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterTRANS_DT] ON
[dbo].[tblpolmaster]([TRANS_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_tblpolmasterPOL_MEMBER] ON
[dbo].[tblpolmaster]([POL_MEMBER]) WITH FILLFACTOR = 90 ON [PRIMARY]
"Andrew J. Kelly" <akelly@targitinteractive.com> wrote in message news:<eqOEVBhHCHA.2456@tkmsftngp08>...
> What kind of indexes do you have? Please show the entire DDL for both
> tables.
>
> --
> Andrew J. Kelly SQL MVP
> Targitinteractive, Inc.
>
>
>
> "Anthony Altieri" <aaltieri@aapconline.com> wrote in message
> news:f9f74e9e.0206271040.5728ab06@posting.google.com...
> > I am new to sql server 2000 and i have been given the task of bringing
> > dbase file using clipper code into
> > sql server 2000 database on a nt4 sp6 machine nice little job. Well
> > here is the problem i have written a
> > stored procedure as follows:
> >
> > the dates in the where clause are parameters, I hard coded them for
> > this example.
> >
> > SELECT tblpolcoverage.AG_NO,
> > tblpolcoverage.TRANS_DT,tblpolcoverage.EFF_DT, tblpolcoverage.EXP_DT,
> > tblpolcoverage.POL_NUM,
> > tblpolcoverage.PREMIUM, tblpolcoverage.TAX, tblpolcoverage.POLFEE,
> > tblpolcoverage.DRAFTAMT, tblpolcoverage.BALANCE,
> > tblpolmaster.POL_LNAME, tblpolmaster.POL_FNAME
> > FROM tblpolcoverage inner join tblpolmaster on
> > tblpolcoverage.masteridx=tblpolmaster.idx
> > WHERE tblpolcoverage.TRANS_DT between '04/01/2002' and '04/30/2002'
> >
> > the two tables tblpolcoverage and tblpolmaster have about 3 million
> > records in them tblpolmaster.idx field
> > relates to tblpolcoverage.masteridx field.
> >
> > returns 209038 rows 48 secs.
> > This is really slow clipper i would be done a long time ago it is hard
> > to explain to my boss that we lost time going foward
> > in software from dbase to sqlserver 2000.
> > Did i really screw this up or is there any hope.
> > Please any help.
| |
| Anthony Altieri 2002-06-28, 9:25 am |
| yes i need those rows it is for a report from vb6 in cyrstal reports8 i
call the sp from there and let the management see these reports there
are at least 100 of them and they take all kinds of different
paramaters. Any other suggestion on how i can report the information to
the management...
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Andrew J. Kelly 2002-06-28, 10:25 am |
| Well obviously don't know what exactly you need but I would assume the
report generator is just summing up these 200K rows to generate a report
that is just a few pages or so. Most reports don't include 200K lines.
Can't you have the sp do the summaries. rollups etc and pass back only what
the report really needs to see? Again you hopefully know what is needed
better than me but I can tell you that returning 200K rows to VB like that
will take longer than you want.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Anthony Altieri" <aaltieri@aapconline.com> wrote in message
news:#a3fNPrHCHA.2464@tkmsftngp12...
> yes i need those rows it is for a report from vb6 in cyrstal reports8 i
> call the sp from there and let the management see these reports there
> are at least 100 of them and they take all kinds of different
> paramaters. Any other suggestion on how i can report the information to
> the management...
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
| |
| Anthony Altieri 2002-06-28, 10:25 am |
|
I will limit them by agent number ag_no and that will limit the
recordset to a couple of 1000's and that is fairly quick. Sorry, yes
they do need the print out every month to verify data entry department
sometimes even on a weekly basis.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
| |
| Andrew J. Kelly 2002-06-28, 12:25 pm |
| If this is strictly a reporting function and it is only done once a month or
even once a week then I wouldn't worry too much about it. Reports are
supposed to be slow<g>. IF they waited all month can't they wait 50
seconds more<g>.
--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.
"Anthony Altieri" <aaltieri@aapconline.com> wrote in message
news:e#ind2rHCHA.2572@tkmsftngp11...
>
> I will limit them by agent number ag_no and that will limit the
> recordset to a couple of 1000's and that is fairly quick. Sorry, yes
> they do need the print out every month to verify data entry department
> sometimes even on a weekly basis.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
|
|
|
|
|