Advice on Perfomance Turning of the Query

[Vimalsoft Pty Ltd]

hi guys I need some advice , i have a Query that runs very long because of the following lines of Code , i know you dont understand my data or table structure , you can just advice on a High Level

			             ( CASE WHEN ( SELECT TOP 1
                                            ATTRIB\_CODE
                                  FROM      SDE.\[NOTIFICATION\] WITH ( NOLOCK )
                                  WHERE     ( LIS\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = LP.LIS\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                              AND FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = V.FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                            )
                                            AND ARCHIVE\_DATE IS NULL
                                ) LIKE '00090009%'
                           THEN ( SELECT TOP 1
                                            ATTRIB\_CODE COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                  FROM      SDE.\[NOTIFICATION\] N WITH ( NOLOCK )
                                  WHERE     N.ARCHIVE\_DATE IS NULL
                                            AND N.FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = V.FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                            AND N.LIS\_KEY  COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = V.LIS\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                            AND N.ATTRIB\_CODE LIKE '00090009%'
                                            AND N.ARCHIVE\_DATE IS NULL
                                            AND V.ARCHIVE\_DATE IS NULL
                                )
                           ELSE ISNULL(( SELECT TOP 1
                                                ATTRIBUTE\_CODE
                                         FROM   SDE.VALUATION WITH ( NOLOCK )
                                         WHERE  ( LIS\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = V.LIS\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                                  AND FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS = V.FUNCTION\_KEY COLLATE SQL\_Latin1\_General\_CP1\_CI\_AS
                                                )
                                                AND VAL\_STATUS\_ID = 2
                                                AND ARCHIVE\_DATE IS NULL
                                       ),

[PIEBALDconsult]

SQL Server? Did you check the execution plan?

[Lost User]

Vuyiswa Maseko wrote:

you can just advice on a High Level

There is a high level checklist for performance here[^]. I'd recommend against optimizer hints unless you can explain how SQL server handles locking. I'd also recommend on removing the collation-checks; it would be set once for the server, and not be repeated in each query as that takes extra time. It also looks a lot like logic that could be run when inserting/updating the record. If that is not helping enough, then look into partitioning the table.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

[Mycroft Holmes]

As a last resort look into Parameter Sniffing[^], I know it sounds like a perversion but it can be an issue. Basically the work around is to create a set of local variables for each parameter passed into the proc and use the local variables in the proc and not the parameters.

Never underestimate the power of human stupidity RAH

[Vimalsoft Pty Ltd]

Thank you very much for the Advice, no my Query runs for 5 Min on 800 000 Records

Vuyiswa Maseko, Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code. C#/VB.NET/ASP.NET/SQL7/2000/2005/2008 http://www.vuyiswamaseko.com vuyiswa[at]dotnetfunda.com http://www.Dotnetfunda.com