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.

Report this post to a moderator

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.

Report this post to a moderator

I believe this is the information you requested and thank you in advance
in helping me.
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]



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Report this post to a moderator

>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.

In most cases SQL Server will may not be as fast as other DB's such as
Foxpro, DBase etc since they don't have all the extra overhead of something
like SQL Server. They are much simpler applications and this must be
expected. I think this really is true when you try to retrieve hundreds of
thousands of rows like this. This is after all a Client / Server based
system and the goal is to return as few rows as possable. SQL Server shines
when have a WHERE clause that is somewhat selective. The hardware also has
a lot to do with performance. DBase does not have a log file and if you
place the log file on the same array as the data you will see decreased
performance, especially with updates. What you do witht he selected data
can have a lot to do with performance as well. Are you bringing all 200K
rows back to a client somewhere? If so this can take a considerable amount
of time due to networks and ADO etc. If you can't limit the amount of rows
then maybe you can further process this data in a sp and only return those
results. As for your query and the tables from what I can see it is
probably using the clustered index on the Date column for the one table but
is most likely scanning or hashing the other. When you have that many
matching rows you limit the effectiveness of an index. Take a look at the
query plan in Query Analyzer and it may give you some clues as to what is
going on. Botom line is you need to ensure that you really need all those
rows at one time. If so then your hardware may be your limiting factor.


--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.



"Anthony Altieri" <aaltieri@aapconline.com> wrote in message
news:ul9b7DqHCHA.2240@tkmsftngp08...
> I believe this is the information you requested and thank you in advance
> in helping me.
> 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]
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Report this post to a moderator

Can you tell us what the query plan looks like?

Attachment: ksherlo.vcf
This has been downloaded 0 time(s).

Report this post to a moderator