Query taking long-long time

13 Jul 2006 7:40 AM

neeju

Hi,

We are having performance problem with the below mentioned query. This
stored procedure execution
takes around 30 hours to complete. This fetches around 18 Million
records.

This process runs in three steps.

1:- Query fetches 18 million records from one database and insert it
into temp table.
2:- Secondly it creates unique index on d_number column in temp
table.
3:- Finally update the records in another table in another database.

I even tried using join instead of subquery but no gain. Please let me
know how can this query
be tuned.

FYI.. all the indexes are properly created and defragmented.

CREATE    procedure dp_GRS_build_md_indc
as

    select d_number
        into #tmp_md_ind
        from grs01..t002_subject_details_and_internal_control
        where marketability_indc = 'M'
        and d_number in(select d_number from grs01..t004_business_address
                    where physical_country_name in ('Northern Ireland',
                                    'Wales' ,
                                    'England' ,
                                    'Scotland' ,
                                    'France',
                                    'Italy' ,
                                    'Germany' ,
                                    'Spain' ,
                                    'Portugal' ,
                                    'Switzerland' ,
                                    'Austria' ,
                                    'Belgium',
                                    'Netherlands',
                                    'Luxembourg' ,
                                    'Norway' ,
                                    'Sweden' ,
                                    'Denmark' ,
                                    'Finland' ,
                                    'Ireland' ))

    create unique index d_unique on #tmp_md_ind(d_number)

    update grs01..t004_business_address
        set md_indc = 'y'
        where d_number in ( select d_number from #tmp_md_ind)

    drop table #tmp_md_ind

GO

13 Jul 2006 8:47 AM

Tony Rogerson

Why use a temporary table?

Start with this and optimise from there, look at using EXISTS rather than
IN.

update grs01..t004_business_address
set md_indc = 'y'
where d_number in (

select d_number
from grs01..t002_subject_details_and_internal_control
where marketability_indc = 'M'
and d_number in(select d_number from grs01..t004_business_address
     where physical_country_name in ('Northern Ireland',
          'Wales' ,
          'England' ,
          'Scotland' ,
          'France',
          'Italy' ,
          'Germany' ,
          'Spain' ,
          'Portugal' ,
          'Switzerland' ,
          'Austria' ,
          'Belgium',
          'Netherlands',
          'Luxembourg' ,
          'Norway' ,
          'Sweden' ,
          'Denmark' ,
          'Finland' ,
          'Ireland' ))
)

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

Show quote

"neeju" <njvds.***@gmail.com> wrote in message
news:1152776438.926561.110800@m79g2000cwm.googlegroups.com...
> Hi,
>
> We are having performance problem with the below mentioned query. This
> stored procedure execution
> takes around 30 hours to complete. This fetches around 18 Million
> records.
>
> This process runs in three steps.
>
> 1:- Query fetches 18 million records from one database and insert it
> into temp table.
> 2:- Secondly it creates unique index on d_number column in temp
> table.
> 3:- Finally update the records in another table in another database.
>
> I even tried using join instead of subquery but no gain. Please let me
> know how can this query
> be tuned.
>
> FYI.. all the indexes are properly created and defragmented.
>
>
> CREATE procedure dp_GRS_build_md_indc
> as
>
>
> select d_number
> into #tmp_md_ind
> from grs01..t002_subject_details_and_internal_control
> where marketability_indc = 'M'
> and d_number in(select d_number from grs01..t004_business_address
> where physical_country_name in ('Northern Ireland',
> 'Wales' ,
> 'England' ,
> 'Scotland' ,
> 'France',
> 'Italy' ,
> 'Germany' ,
> 'Spain' ,
> 'Portugal' ,
> 'Switzerland' ,
> 'Austria' ,
> 'Belgium',
> 'Netherlands',
> 'Luxembourg' ,
> 'Norway' ,
> 'Sweden' ,
> 'Denmark' ,
> 'Finland' ,
> 'Ireland' ))
>
> create unique index d_unique on #tmp_md_ind(d_number)
>
>
> update grs01..t004_business_address
> set md_indc = 'y'
> where d_number in ( select d_number from #tmp_md_ind)
>
>
> drop table #tmp_md_ind
>
> GO
>

13 Jul 2006 9:13 AM

Chris Lim

neeju wrote:
Show quote

> Hi,
>
> We are having performance problem with the below mentioned query. This
> stored procedure execution
> takes around 30 hours to complete. This fetches around 18 Million
> records.
>
> This process runs in three steps.
>
> 1:- Query fetches 18 million records from one database and insert it
> into temp table.
> 2:- Secondly it creates unique index on d_number column in temp
> table.
> 3:- Finally update the records in another table in another database.

First of all, please post DDL including constraints and indexes so we
don't have to guess.

Secondly, from what I can tell I don't think you need to do this
process in 3 steps.

Below are a couple of ways to do it in one update (unless I
misunderstand the requirements). Note that some people don't like using
UPDATEs in this way as it's not ANSI compliant, but I've found these to
be more efficient than writing sub-queries. YMMV.

Also, try without using SET FORCEPLAN ON first. This is a last resort
that forces the optimiser to process the tables in the order specified
in the query. Again, some people don't like this, but I've found at
times it improves performance dramatically.

The difference between the 2 versions is purely the order of the tables
(and hence only applicable with FORCEPLAN ON). Which version is better
depends on your data. If it's better to filter out the
t004_business_address table first based on the address (which I'm
assuming you have an index on), then the first version would be better.
OTOH if it's better (i.e. more selective) to filter
t002_subject_details_and_internal_control where sdi.marketability_indc
= 'M' first, then the second version may be better.

CREATE    procedure dp_GRS_build_md_indc
as

        SET FORCEPLAN ON

        UPDATE grs01..t004_business_address
        SET     md_indc = 'y'
        FROM grs01..t004_business_address ba
        INNER JOIN grs01..t002_subject_details_and_internal_control
sdi
          ON sdi.d_number = ba.d_number
          AND sdi.marketability_indc = 'M'
        WHERE ba.physical_country_name in ('Northern Ireland',
                                           'Wales' ,
                                           'England' ,
                                           'Scotland' ,
                                           'France',
                                           'Italy' ,
                                           'Germany' ,
                                           'Spain' ,
                                           'Portugal' ,
                                           'Switzerland' ,
                                           'Austria' ,
                                           'Belgium',
                                           'Netherlands',
                                           'Luxembourg' ,
                                           'Norway' ,
                                           'Sweden' ,
                                           'Denmark' ,
                                           'Finland' ,
                                           'Ireland' )

        SET FORCEPLAN OFF

GO

CREATE    procedure dp_GRS_build_md_indc
as

        SET FORCEPLAN ON

        UPDATE grs01..t004_business_address
        SET     md_indc = 'y'
        FROM grs01..t002_subject_details_and_internal_control sdi
        INNER JOIN grs01..t004_business_address ba
          ON ba.d_number = sdi.d_number
          AND ba.physical_country_name in ('Northern Ireland',
                                           'Wales' ,
                                           'England' ,
                                           'Scotland' ,
                                           'France',
                                           'Italy' ,
                                           'Germany' ,
                                           'Spain' ,
                                           'Portugal' ,
                                           'Switzerland' ,
                                           'Austria' ,
                                           'Belgium',
                                           'Netherlands',
                                           'Luxembourg' ,
                                           'Norway' ,
                                           'Sweden' ,
                                           'Denmark' ,
                                           'Finland' ,
                                           'Ireland' )
        WHERE sdi.marketability_indc = 'M'

        SET FORCEPLAN OFF

GO

13 Jul 2006 9:28 AM

neeju

Thanks Tony/Chris for taking some time out for this.

I have not tried the query yet using "EXIST" as tony suggested. Well
eager to try that.

Though i have also removed temp table and restructured the query using
single Update Query quite similar to the second query you suggested
ofcourse without "Set ForcePlan On".

This single update query still running and taken around 10 hours by
now. We are waiting to see when it gets finished so that anything else
can be tried.

Even if we reduce the execution time by 10 hours that would be good for
the time being.

Thanks,
NJ

Chris Lim wrote:
Show quote

> neeju wrote:
> > Hi,
> >
> > We are having performance problem with the below mentioned query. This
> > stored procedure execution
> > takes around 30 hours to complete. This fetches around 18 Million
> > records.
> >
> > This process runs in three steps.
> >
> > 1:- Query fetches 18 million records from one database and insert it
> > into temp table.
> > 2:- Secondly it creates unique index on d_number column in temp
> > table.
> > 3:- Finally update the records in another table in another database.
>
> First of all, please post DDL including constraints and indexes so we
> don't have to guess.
>
> Secondly, from what I can tell I don't think you need to do this
> process in 3 steps.
>
> Below are a couple of ways to do it in one update (unless I
> misunderstand the requirements). Note that some people don't like using
> UPDATEs in this way as it's not ANSI compliant, but I've found these to
> be more efficient than writing sub-queries. YMMV.
>
> Also, try without using SET FORCEPLAN ON first. This is a last resort
> that forces the optimiser to process the tables in the order specified
> in the query. Again, some people don't like this, but I've found at
> times it improves performance dramatically.
>
> The difference between the 2 versions is purely the order of the tables
> (and hence only applicable with FORCEPLAN ON). Which version is better
> depends on your data. If it's better to filter out the
> t004_business_address table first based on the address (which I'm
> assuming you have an index on), then the first version would be better.
> OTOH if it's better (i.e. more selective) to filter
> t002_subject_details_and_internal_control where sdi.marketability_indc
> = 'M' first, then the second version may be better.
>
> CREATE    procedure dp_GRS_build_md_indc
> as
>
>         SET FORCEPLAN ON
>
>         UPDATE grs01..t004_business_address
>         SET     md_indc = 'y'
>         FROM grs01..t004_business_address ba
>         INNER JOIN grs01..t002_subject_details_and_internal_control
> sdi
>           ON sdi.d_number = ba.d_number
>           AND sdi.marketability_indc = 'M'
>         WHERE ba.physical_country_name in ('Northern Ireland',
>                                            'Wales' ,
>                                            'England' ,
>                                            'Scotland' ,
>                                            'France',
>                                            'Italy' ,
>                                            'Germany' ,
>                                            'Spain' ,
>                                            'Portugal' ,
>                                            'Switzerland' ,
>                                            'Austria' ,
>                                            'Belgium',
>                                            'Netherlands',
>                                            'Luxembourg' ,
>                                            'Norway' ,
>                                            'Sweden' ,
>                                            'Denmark' ,
>                                            'Finland' ,
>                                            'Ireland' )
>
>         SET FORCEPLAN OFF
>
> GO
>
>
> CREATE    procedure dp_GRS_build_md_indc
> as
>
>         SET FORCEPLAN ON
>
>         UPDATE grs01..t004_business_address
>         SET     md_indc = 'y'
>         FROM grs01..t002_subject_details_and_internal_control sdi
>         INNER JOIN grs01..t004_business_address ba
>           ON ba.d_number = sdi.d_number
>           AND ba.physical_country_name in ('Northern Ireland',
>                                            'Wales' ,
>                                            'England' ,
>                                            'Scotland' ,
>                                            'France',
>                                            'Italy' ,
>                                            'Germany' ,
>                                            'Spain' ,
>                                            'Portugal' ,
>                                            'Switzerland' ,
>                                            'Austria' ,
>                                            'Belgium',
>                                            'Netherlands',
>                                            'Luxembourg' ,
>                                            'Norway' ,
>                                            'Sweden' ,
>                                            'Denmark' ,
>                                            'Finland' ,
>                                            'Ireland' )
>         WHERE sdi.marketability_indc = 'M'
>
>         SET FORCEPLAN OFF
>
> GO

13 Jul 2006 9:43 AM

Chris Lim

neeju wrote:

> Though i have also removed temp table and restructured the query using
> single Update Query quite similar to the second query you suggested
> ofcourse without "Set ForcePlan On".

If you see no performance gains with either of the queries I suggested,
it would be good if you could post the query plans of each of them so
we can see which index(es) are being used.

Chris

13 Jul 2006 5:38 PM

Chris Lim

neeju wrote:

> Thanks Tony/Chris for taking some time out for this.

Also forgot to ask you, with your temp table query, do you know which
part takes the most time? Is it the select into the temp table, the
create index, or the update?

If it's the update that's slow rather than the selecting of data to be
updated, then it may just be that it takes that long to update that
many rows at once (18 million did you say?). If that's the case, then
updating in smaller blocks (e.g. 1 million rows at a time) may help.

Chris

13 Jul 2006 11:23 AM

Roy Harvey

I rewrote this using EXISTS. I also found that all the joining was on
d_number, which made the nesting optional, so I did away with it and
ended up with two EXISTS tests.

UPDATE grs01..t004_business_address
   SET md_indc = 'y'
WHERE EXISTS
       (select * from grs01..t002_subject_details_and_internal_control
as X
         where marketability_indc = 'M'
           and grs01..t004_business_address.d_number = X.d_number)
   AND EXISTS
       (select * from grs01..t004_business_address as Y
         where grs01..t004_business_address.d_number = Y.d_number
           and physical_country_name IN
               ('Northern Ireland', 'Wales', 'England', 'Scotland',
                'France', 'Italy', 'Germany', 'Spain', 'Portugal',
                'Switzerland', 'Austria', 'Belgium', 'Netherlands',
                'Luxembourg', 'Norway', 'Sweden', 'Denmark',
                'Finland', 'Ireland'))

Roy Harvey
Beacon Falls, CT

Show quote

On 13 Jul 2006 00:40:39 -0700, "neeju" <njvds.***@gmail.com> wrote:

>Hi,
>
>We are having performance problem with the below mentioned query. This
>stored procedure execution
>takes around 30 hours to complete. This fetches around 18 Million
>records.
>
>This process runs in three steps.
>
>1:- Query fetches 18 million records from one database and insert it
>into temp table.
>2:- Secondly it creates unique index on d_number column in temp
>table.
>3:- Finally update the records in another table in another database.
>
>I even tried using join instead of subquery but no gain. Please let me
>know how can this query
>be tuned.
>
>FYI.. all the indexes are properly created and defragmented.
>
>
>CREATE    procedure dp_GRS_build_md_indc
>as
>
>
>    select d_number
>        into #tmp_md_ind
>        from grs01..t002_subject_details_and_internal_control
>        where marketability_indc = 'M'
>        and d_number in(select d_number from grs01..t004_business_address
>                    where physical_country_name in ('Northern Ireland',
>                                    'Wales' ,
>                                    'England' ,
>                                    'Scotland' ,
>                                    'France',
>                                    'Italy' ,
>                                    'Germany' ,
>                                    'Spain' ,
>                                    'Portugal' ,
>                                    'Switzerland' ,
>                                    'Austria' ,
>                                    'Belgium',
>                                    'Netherlands',
>                                    'Luxembourg' ,
>                                    'Norway' ,
>                                    'Sweden' ,
>                                    'Denmark' ,
>                                    'Finland' ,
>                                    'Ireland' ))
>
>    create unique index d_unique on #tmp_md_ind(d_number)
>
>
>    update grs01..t004_business_address
>        set md_indc = 'y'
>        where d_number in ( select d_number from #tmp_md_ind)
>
>
>    drop table #tmp_md_ind
>
>GO

13 Jul 2006 1:01 PM

jsfromynr

Hi There,
You may like to try this.

Find out the execution Plan for this statement(Your)

select d_number
                into #tmp_md_ind
                from grs01..t002_subject_details_and_internal_control
                where marketability_indc = 'M'
                and d_number in(select d_number from
grs01..t004_business_address
                                        where physical_country_name in
('Northern Ireland',

'Wales' ,

'England' ,

'Scotland' ,

'France',

'Italy' ,

'Germany' ,

'Spain' ,

'Portugal' ,

'Switzerland' ,

'Austria' ,

'Belgium',

'Netherlands',

'Luxembourg' ,

'Norway' ,

'Sweden' ,

'Denmark' ,

'Finland' ,

'Ireland' ))

And these Two , See if it can help you
Have an index on physical_country_name or may be better if you have a
covering index on
physical_country_name , d_number

select d_number
                into #tmp_md_ind
                from grs01..t002_subject_details_and_internal_control M
                where marketability_indc = 'M'
                and Exists (
            select d_number from grs01..t004_business_address T
                                        where M.d_number=T.d_number and

                    physical_country_name ='Northern Ireland'
                Union All
            select d_number from grs01..t004_business_address T
                                        where M.d_number=T.d_number and

                    physical_country_name ='Wales'
....... and so on
)

Or

select d_number
                into #tmp_md_ind
                from grs01..t002_subject_details_and_internal_control M
                where marketability_indc = 'M'
                d_number In (
            select d_number from grs01..t004_business_address T
                                        where physical_country_name
='Northern Ireland'
                Union All
            select d_number from grs01..t004_business_address T
                                        where physical_country_name
='Wales'
....... and so on
)

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com