0

I am having a SQL query which is sheduled to run on every week and pulls the data from different database and the query is running for around 2 hrs this is due to the amount of data it is selecting, on the same time this is utilizing more CPU utilization on the source SQL server where database abc resides. The query is given below,

select a.* from abc.art_si a inner join abc.article b 
                    on a.ARTICLEID = b.ARTICLEID where b.TYPE_IND='B'

I would like to know the below,

  1. running of this query will utilize more CPU? If so,
  2. is there any way to optimize the above query?

Your advise will be very helpful for me.

Thank you.

Chandru
  • 33
  • 1
  • 1
  • 7
  • 1
    Please can you provide some extra information: 1) what RDBMS, 2) what is the general setuo e.g. data from server A, being called by sql server agent, transferred to server b and 3) the structure of the table including indexes and row counts – Steph Locke Nov 29 '13 at 12:44
  • 2
    1- never `Select *` in production code. 2- Have you tried any kind of profiling/performance testing? – AllenG Nov 29 '13 at 12:45
  • Hi Steph, 1. Source is DB2 database 2. the job is being called by a scheduler called BMC Control-m and which in turn triggers a SSIS package by a Batch file. Selecting from the source and inserting into the server b (which is SQL 2008 DB) 3. As it is from the another system i will get the info and supply the same. – Chandru Nov 29 '13 at 13:01
  • Hi Allen, the performance test have not tried earlier since it had very low records and now the data growth has increased which is increasing the CPU utilization on the source server. – Chandru Nov 29 '13 at 13:03
  • 1
    Ah, that's a tangled web of bits and pieces you got there. The issue might not be the select statement - there could be hardware issues (network throughput, RAM etc), or there could be software issues (type_ind not being indexed, the SSIS package doing something weird). I recommend starting at the beginning of the chain and moving step by through the process to see where the performance killer comes in, so start by running the select statement directly on db2 and see how quickly it responds. – Steph Locke Nov 29 '13 at 13:09
  • @AllenG - never say never. "never Select * in production code" is not a valid assumption - [can select * be justified](http://stackoverflow.com/questions/3635781/can-select-usage-ever-be-justified) – Yosi Dahari Nov 29 '13 at 13:16
  • 1
    @Yosi- And yet... "never" is a closer rule than "just whenever." As someone in that link said: "Rules and best practices are great, as long as you know when to break them." With which I don't disagree. – AllenG Nov 29 '13 at 13:28
  • You might find this useful, if you don't have control over things like indexes and you have to optimise your joining there are instances where an outer join would be beneficial - http://stackoverflow.com/questions/4038769/is-join-or-where-clause-approach-more-efficient-in-restricting-result-set – talegna Nov 29 '13 at 14:09
  • @StephLocke additional information on the row details, Art_site has 64.2 million records. Article has 2.4 million and after applying the where condition as 'B' the output is 80.9 thousand rows. Hence is there any chances for more CPU utilization during the data selection? – Chandru Nov 29 '13 at 17:24
  • Consider changing the order on the tables so you do `Article` first. The number of rows is lower on that table so it should gain you some performance improvement – Steph Locke Dec 02 '13 at 09:39
  • @StephLocke : I will look for changing the order of tables and feedback you further. Thank you so much for the advise. – Chandru Dec 04 '13 at 14:25

0 Answers0