Is it the Index ?

[frank niedermeyer]

Hello Newsgroup !

I'm working with SQL Server 7.0 and Access 2000 .
I build some stored procedures and indexes to enhance the performance of my
queries.

Now there is another application (blackbox) which imports new data to my
only database table.
Once it took 1 ms for one file (1kb) today it takes 7 min. I know there is
something wrong. Is it
possible that this happens because of my index ?

The table has 10 million rows.

Greetings

Frank

[Lyndon Hills]

Re: Is it the Index ?

On Wed, 10 Dec 2003 16:49:50 +0100, "frank niedermeyer"
<fniedermeyer@a areon.com> wrote:
[color=blue]
>Hello Newsgroup !
>
>I'm working with SQL Server 7.0 and Access 2000 .
>I build some stored procedures and indexes to enhance the performance of my
>queries.
>
>Now there is another application (blackbox) which imports new data to my
>only database table.
>Once it took 1 ms for one file (1kb) today it takes 7 min. I know there is
>something wrong. Is it
>possible that this happens because of my index ?
>
>The table has 10 million rows.
>
>Greetings
>
>Frank
>[/color]
The short answer is yes, it's possible. An index speeds the retrieval
of data, but makes insertion and update slower. That's a rather
dramatic slowdown, however.

Other things to consider

You say 'once' it was 1ms, today 7 minutes. When was 'once'? How many
rows have been added since then?

How is this file loaded? (Insert statement or stored proc / dts /
other).
Are there any triggers on the table?
Are there any checks that would be performed when loading rows?
How were the table and index created? free space etc. What type of
index?
Is the database maintained?

If you have enterprise manager there are the performance tuning tools
like profiler, you could look at.

You will get more help if you post the ddl for this table and indexes,
and answer some of the points above.