• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Query takes long time

I have a two queries (see below). The provider Master table has numerous records - it is the entire provider registry. If this query is run on a specific SQl server
box it never completes ..takes long time to run. Is there a way to simplify this to avoid these JOIN on provider master table.
Or Is there a way to find out why it takes so long. The profiler just is stuck on these queries.

/* Mark records from manual update when Tax numbermatches but organization name does not match in provider master table. */

      INSERT ExceptionTableLog
      SELECT distinct (m.Tiffilename), 20,'N','Y'
      FROM tablemain m
            JOIN ProviderMasterTable pm ON (pm.prvmstid = m.[TaxID])
      where not exists
            (select 1 from ProviderMaster pm where pm.prvmstfullname = isNULL (m.[orgname], pm.prvmstfullname)
                                                                                                                          and pm.prvmstid = m.[TaxID])


** If TaxID matches, Org IS NULL  AND (fname, minitial, lname) match,
** but addr1 does not match
**      then mark for manual update

      INSERT ExceptionTableLog
      (tif,id,aid,mid)
      SELECT DISTINCT(tif)      ,20,'N','Y'
      FROM tablemain m
            JOIN ProviderMaster pm ON (m.[TaxID] = pm.prvmstid
                              AND  m.[LastName] = pm.prvmstlname
                              AND  m.[Address] <> pm.prvmstaddr1)
            LEFT JOIN  ProviderMaster pm2 ON (m.[TaxID] = pm2.prvmstid
                        AND  m.[LastName] = pm2.prvmstlname
                        AND  m.[Address] = pm2.prvmstaddr1)

      WHERE (m.[OrgName] IS NULL )
      AND pm2.prvmstid IS NULL
      AND NOT EXISTS
            (SELECT 1
             FROM ExceptionLogTable l
             WHERE l.tif = m.tif
               AND l.hcfatrid = 20)
0
priyums
Asked:
priyums
  • 4
  • 3
1 Solution
 
dbeneitCommented:
this is because you are using tablemain m in a not exist select.. then you launch a distinct select  for every row

You can test:

 INSERT ExceptionTableLog
     (tif,id,aid,mid)
     SELECT DISTINCT(tif)     ,20,'N','Y'
     FROM tablemain m
          JOIN ProviderMaster pm ON (m.[TaxID] = pm.prvmstid
                         AND  m.[LastName] = pm.prvmstlname
                         AND  m.[Address] <> pm.prvmstaddr1)
          LEFT JOIN  ProviderMaster pm2 ON (m.[TaxID] = pm2.prvmstid
                    AND  m.[LastName] = pm2.prvmstlname
                    AND  m.[Address] = pm2.prvmstaddr1)
          LEFT JOIN ExceptionLogTable l ON ( l.tif = m.tif
              AND l.hcfatrid = 20)
     WHERE (m.[OrgName] IS NULL ) and l.tif  is null
     AND pm2.prvmstid IS NULL
     
0
 
dbeneitCommented:
and

     INSERT ExceptionTableLog
     SELECT distinct (m.Tiffilename), 20,'N','Y'
     FROM (tablemain m
          JOIN ProviderMasterTable pm ON (pm.prvmstid = m.[TaxID]))
          left join ProviderMaster pm2 where pm2.prvmstfullname = isNULL (m.[orgname], pm2.prvmstfullname)
     where pm2.prvmstfullname is null    
0
 
priyumsAuthor Commented:
How come you got  t.tif is NULL in
tha last line
 and l.tif  is null
   
I got most of the logic except this one. Why are you checking for l.tif is null . Does that cover the NOT EXISTS.. logic
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
priyumsAuthor Commented:
Got it thanks! I will have the database only little later to test. But I got the answer.
0
 
dbeneitCommented:
Yes, "not exist" can be equivalen in a Left JOIN when a right part is null.
I'm checking for I.tif because is a field that I used in "LEFT JOIN ExceptionLogTable l ON ( l.tif = m.tif"
and it's the unique name field i know. :-)
0
 
priyumsAuthor Commented:
Thanks, What you did did save some time. But still teh whole thing takes ridiculous amount of time..It is running for more than like 30 minutes now..
I am watching the profiler and it is stuck on the first statement'
INSERT ExceptionTableLog
     SELECT distinct (m.Tiffilename), 20,'N','Y'
     FROM (tablemain m
          JOIN ProviderMasterTable pm ON (pm.prvmstid = m.[TaxID]))
          left join ProviderMaster pm2 where pm2.prvmstfullname = isNULL (m.[orgname], pm2.prvmstfullname)
     where pm2.prvmstfullname is null    

I dont know the provider table has more than like 25000 records..May be thats a problem. I am also checking logging. By default a SQL log is created. Other than that there is no logging.
0
 
priyumsAuthor Commented:
You ahve to give point to dbeneit . His solution did make the query faster
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now