Re: composite key question
From: Robert Bouillon (djwhizzard_at_hotmail.com)
Date: 09/16/04
- Next message: Robert Bouillon: "Re: HELP! VB.NET killed SQL Server"
- Previous message: Mark H: "RE: proc argument type array"
- In reply to: Greg Burns: "Re: composite key question"
- Next in thread: Hugo Kornelis: "Re: composite key question"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 16 Sep 2004 17:11:39 -0400
I understand now.
I'm sure that the fact that there are 8 indexes for 2 million rows is
greatly affecting the speed of the insert. Each time you do an insert SQL
has to do some reclalculating of the indexes, depending on how they're set
up.
The first thing I'd recommend is removing the composite index. If the ID
field is acting as an identity column, that's (probably) what it should be,
and there's no need to have library_id in there as well. A conservative PK
structure alsmost always boosts performance, but there may be a reason they
have it set up like that. Whether it's a good reason or not remains to be
seen.
Secondly, it's probably not a stored procedure. The script should be
converted to one and possibly tweaked with the below suggestions. Profiler
and Analyzer (using Show Execution Path) can be used to further tweak the
query. I don't think moving it to a proc will make a huge difference
directly, but it will give the developers a little more power so they can
create temp values, tables, etc.
You could tweak the indexes, yes, but without having a good idea about
how/where they're used, you may end up doing more harm than good. Plus, with
8 indexes and 2 million records, I doubt any amount of "Tweaking" will give
any noticable performance boost.
Often novice developers will create indexes at a whim merely because they
call 'WHERE' on the field or use it in a join to improve the speed of thier
queries. If you're doing more inserts than selects, or the query (select)
time isn't as important as the insert time, I would say drop as many indexes
as possible. Dropping an index shouldn't break anything: it will cause some
queries to perform slower, however.
The problem may be in the scripting of the queries that request info from
that table, which is what probably prompted the creation of the indexes.
Seeing as how that script you sent was not a stored proc, if you're going to
make a recommendation, it should be:
Drop the indexes (as many as possible), make the ID column and indentity
(and drop the library_id from the PK), convert as many scripts as possible
to stored procs, and optimize thier SQL scripts to compensate for the lack
of Indexes (There are MANY articles on DO's and DONT's of T-SQL programming
that would shed a lot of light on slow queries).
I can't do much more without actually seeing the database. Maybe hiring a
DBA to look at the database would be the next logical step (If it's that
important). Let me know if this helps or if you need more info.
--ROBERT
"Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
news:%23FnvakCnEHA.3520@TK2MSFTNGP11.phx.gbl...
> > Do you have any other indexes on your tbl_Log table?
> Yes, they have and 8(!) column Unique Index on some of the others fields
(I
> didn't show them in the example)
>
> > Are you using a Stored Procedure?
> dunno (see below)
>
> > Lastly, it seems you want ID to be and IDENTITY column. Why not let SQL
> > seed
> > it? Why use a composite index if ID uniquely identifies your row?
>
> Unfortunately, I didn't write the code nor can I change how it is written.
> This SQL statement was pulled from Profiler. I was looking to see why a
3rd
> party app we are using is hogging SQL's cpu to 100% for hours while it
runs.
>
> I am not sure what I hope to accomplish, but I thought maybe their was an
> index I could tweak without breaking anything.
>
> Or perhaps have a suggestion about a change that I could fwd to the app's
> developers for their next release
>
> Greg
>
>
> "Robert Bouillon" <djwhizzard@hotmail.com> wrote in message
> news:uMCr75BnEHA.324@TK2MSFTNGP11.phx.gbl...
> > How many records are typically being insterted?
> > Do you have any other indexes on your tbl_Log table?
> > Are you using a Stored Procedure?
> >
> > Try running the select seperately from the INSERT to see which one is
> > taking
> > longer. Indexes make INSERTS take longer.
> >
> > I'm not sure, but you might be asking SQL to perform the MAX aggregate
> > twice
> > per record. That would be a problem, but maybe someone else could shed
> > some
> > light on that.
> >
> > It looks to me like to me like you're asking ID to act like an identity
> > column. As long as you have one record in there, you don't need to
perform
> > the case on every record. If that's the case then try something like
this:
> >
> > BEGIN TRANSACTION
> >
> > INSERT INTO tbl_log (1, <dummydata>)
> > IF @@ERROR<>0 GOTO HANDLEERROR
> >
> > INSERT INTO tbl_log (id, <somevalue>)
> > SELECT MAX(id), <somevalue>
> > FROM tbl_log
> > WHERE library_id = 1
> > IF @@ERROR<>0 GOTO HANDLEERROR
> >
> > DELETE FROM tbl_Log WHERE ID = 1
> > IF @@ERROR<>0 GOTO HANDLEERROR
> >
> > HANDLEERROR:
> > IF(@@ERROR<>0)
> > ROLLBACK
> > ELSE
> > COMMIT
> >
> > If not, this may add a small performance gain:
> >
> > INSERT INTO tbl_log (id, <somevalue>)
> > SELECT ISNULL(MAX(id),1) + 1, <somevalue>
> > FROM tbl_log
> > WHERE library_id = 1
> >
> > Lastly, it seems you want ID to be and IDENTITY column. Why not let SQL
> > seed
> > it? Why use a composite index if ID uniquely identifies your row?
> >
> > --ROBERT
> >
> >
> > "Greg Burns" <greg_burns@DONT_SPAM_ME_hotmail.com> wrote in message
> > news:uGfi6tBnEHA.3712@TK2MSFTNGP15.phx.gbl...
> >> We have a table that contains a composite primary key: id and
library_id
> >>
> >> Below is the current code for inserting into a history table. Each new
> >> record get assigned the next available id number per id and library_id
> > pair.
> >>
> >> Is there a more efficient way of doing this?
> >>
> >> Our table has over 2 million rows, and this takes a very log time to
> >> execute.
> >>
> >> INSERT INTO tbl_log (id, <somevalue>)
> >> SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id) + 1 END,
<somevalue>
> >> FROM tbl_log
> >> WHERE library_id = 1
> >>
> >> Would adding an index either field help?
> >>
> >> TIA,
> >> Greg
> >>
> >>
> >
> >
>
>
- Next message: Robert Bouillon: "Re: HELP! VB.NET killed SQL Server"
- Previous message: Mark H: "RE: proc argument type array"
- In reply to: Greg Burns: "Re: composite key question"
- Next in thread: Hugo Kornelis: "Re: composite key question"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
