Re: Query
From: Jaco (Jaco_at_discussions.microsoft.com)
Date: 02/10/05
- Next message: Cymryr: "Re: Databse Design problem"
- Previous message: Tibor Karaszi: "Re: dbcc checktable(syslogs)"
- In reply to: Hugo Kornelis: "Re: Query"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Feb 2005 01:51:05 -0800
Thanks.
"Hugo Kornelis" wrote:
> On Wed, 9 Feb 2005 03:07:02 -0800, Jaco wrote:
>
> >sorry -
> >
> >CREATE PROCEDURE qfm_GetEventSearchResult
> >@param nvarchar(4000)= NULL,
> >@maxRecords nvarchar(10) = NULL
> >AS
> (snip)
>
> Hi Jaco,
>
> Are you really sure that this is the only way to address your needs?
> Usually, passing a where clause to a stored procedure is a token of a weak
> design. Besides, it is incrediblyu dangerous - your system is wide open to
> SQL injection attacks (see http://www.sommarskog.se/dynamic_sql.html for
> an excellent explenations of these and other dangers of dynamic SQL).
>
> If I didn't miscount, you are joining a total of THIRTY-SEVEN tables in
> this join. And only one of them is joined using inner join, all others are
> joined with a left or right outer join. Surely, you didn't _expect_ great
> performance from this query?
>
> Some tips that might (or might not - you'll have to try) help you speed
> this up:
>
> * Eliminate tables you don't need. You use a dynamically passed where
> clause - do you join in some extra tables, just so they can be used in the
> where clause if needed? If so, then eliminate them - you're just making
> the DB engine do needless extra work.
>
> * Remove the distinct. It's quite possible that you need it because you
> join in to many tables; remove the unneeded tables, and the distinct
> becomes redundant as well.
>
> * Check the user-defined functions you are using. Could they be a
> bottleneck? Remember that SQL Server will call them for each row in the
> result set. If 80 duplicate rows get removed because of the DISTINCT, each
> of the functions has already been called 80 times. If possible, remove
> them or put the function's logic inline (so that SQL Server has more
> optimization options).
>
> * If you use a join only to test for existance or non-existance of a row
> in a table, change it to [NOT] EXISTS (subquery).
>
> * Change LEFT and RIGHT joins to INNER joins wherever possible.
>
> * Change the way you call this proc. Instead of
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
> "2005/Feb/09")
> AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
> AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
> use
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "20050126" AND
> "20050209")
> AND (Assignee.Name = "Beattie Steve")
> AND (OnBehalf.Name = "Beattie Steve")', '500'
> (That is - use an unambiguous date format to prevent misconversions and
> use equality instead of BETWEEN, since the upper and lower limit of the
> between range are equal anyway)
>
> * Check the execution plan. Which tables are being scanned? Which are
> accessed using an index? Which are accessed very often? Maybe adding some
> indexes will gain you some performance.
>
> * Manage expectations :-) If you feed SQL Server a humongous query that
> joins this amount of tables and calls several functions to boot, you can't
> expect great performance. I'm afraid that the compilation of an execution
> plan will already take quite some time - just consider the enormous number
> of strategies the optimizer will have to choose from!
>
> * And the most important advice: reconsider if you really need this
> procedure to operate as it now does.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
- Next message: Cymryr: "Re: Databse Design problem"
- Previous message: Tibor Karaszi: "Re: dbcc checktable(syslogs)"
- In reply to: Hugo Kornelis: "Re: Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
