RE: Preformance issue

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Mal .mullerjannie_at_hotmail.com> (.mullerjannie_at_hotmail.com)
Date: 02/03/05 

Date: Thu, 3 Feb 2005 08:19:04 -0800

Edi

I'm not going to paste code for you but I am going to tell you that you will
get performance increase if you the remove the "in (subquery)" rather do
a join on your subquery as a table , in is effectively an "or" and is slow.

Hope it help.

It's not a lot of code to change,

Just isolate each subquery into (Select this from that) and give an alias
and join it to the next one. It's maybe easier said than done but I hope you
see what I mean.

"Edi" wrote:

> Hello,
> I’m currently working with SQL server and I’m SQL query generator that work
> real fine for my proposes. In the last days im working on using my SQL query
> generator with Access Database. The problem is that some group of queries
> takes really long time to process in Access (7-9 minutes. In SQL Server it
> takes less then a second). Example for problematic query:
>
> SELECT [Col016], [Col014], [Col015], [Col045], [Col043], [Col044], SUM
> (CAST ([Col098] AS Float)) AS [AggrigatedCol098], 0 AS [OrderingColmun]
> FROM [Drek]
> WHERE ISNULL(CAST ([Col016] AS VARCHAR), '') + ISNULL(CAST ([Col014] AS
> VARCHAR), '') + ISNULL(CAST ([Col015] AS VARCHAR), '') IN
> (
> SELECT [Unique]
> FROM (
> SELECT DISTINCT ISNULL(CAST ([Col016] AS VARCHAR), '') + ISNULL(CAST
> ([Col014] AS VARCHAR), '') + ISNULL(CAST ([Col015] AS VARCHAR), '') AS
> [Unique], [Col016], [Col014], [Col015]
> FROM [Drek]
> WHERE ( ( Col016 = '[Products].[All Products].[Communications].[Networking
> Devices].[Platford P12]') OR ( Col016 = '[Products].[All
> Products].[Communications].[Networking Devices].[Mark 8 Rack]') OR ( Col016
> = '[Products].[All Products].[Computer Hardware].[PCs].[Alpha IIp633]') )
> GROUP BY [Col016], [Col014], [Col015]
> ) AS [TempSelectMembers]
> )
> AND ISNULL(CAST ([Col045] AS VARCHAR), '') + ISNULL(CAST ([Col043] AS
> VARCHAR), '') + ISNULL(CAST ([Col044] AS VARCHAR), '') IN (
> SELECT [Unique]
> FROM (
> SELECT DISTINCT ISNULL(CAST ([Col045] AS VARCHAR), '') + ISNULL(CAST
> ([Col043] AS VARCHAR), '') + ISNULL(CAST ([Col044] AS VARCHAR), '') AS
> [Unique], [Col045], [Col043], [Col044]
> FROM [Drek]
> WHERE ( ( Col045 = '[Stores].[All Stores].[North
> Amer].[USA].[Washington].[Tacoma].[Store-466]') OR ( Col045 = '[Stores].[All
> Stores].[North Amer].[USA].[Florida].[Miami].[Store-484]') OR ( Col045 =
> '[Stores].[All Stores].[Europe].[Germany].[Germany].[Munchen].[Store-602]')
> )
> GROUP BY [Col045], [Col043], [Col044]
> ) AS [TempSelectMembers]
> )
> GROUP BY [Col016], [Col014], [Col015], [Col045], [Col043], [Col044]
>
>
> The reason that im using the “WHERE IN (….)” is that is a systematic way of
> building query from a given other member query, so, I can’t avoid it.
>
> Thanks

Relevant Pages

  • Re: how to improve performance of LEFT JOIN
    ... LEFT OUTER JOIN TableB ... I add the subquery to query every table before 'LEFT JOIN' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Subqueries
    ... I have a query which contains a subquery for a field value. ... The query works in SQL Server but not SQL Server CE. ... SELECT TABLE2.ITEMNMBR, TABLE2.ITEMDESC, SumAs QTYONHND, LOCNCODE, ...
    (microsoft.public.sqlserver.ce)
  • Subqueries
    ... >I have a query which contains a subquery for a field ... The query works in SQL Server but not SQL Server ... = 'MyLocation' GROUP BY ...
    (microsoft.public.sqlserver.ce)
  • Re: Different results from similar code
    ... >SET ROWCOUNT 0 ... the order by is in a subquery. ... updated in whatever SQL Server chooses to use as execution plan. ... If you remove the SET ROWCOUNT from the first query, ...
    (microsoft.public.sqlserver.programming)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)