Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    vba code hangs use 100% usage of PC

    Hi All,


    I have a big issue i have create some sql queries in vba it runs but took all the cpu usage up to 100 % a code take 30 + mins to get data in excel sheet , please help how to avoid this 100% usage?

    Thank you

    Farrukh

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    Sometimes the reason for the slow response time is due to the load on the system, and other times it is because the query is not written to perform as efficiently as possible.
    In most cases it's the SQL query that could be 'improved' to get the same results faster.
    Perhaps you could just post your sql query for us to look at.

    For example, any functions using the WHERE clause will cause SQL Server to perform the function on every row being searched, which means the index will not be used.
    Code can be rewritten so that an index is used, and the results are returned in a much quicker fashion.

    zeddy

  3. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Hi Zeddy ,

    The problem i have is that this query run range means i pass the date like 1/1/2000 to 1/31/2012 it calculate the sum of every variable from the start date of any created equipment to every month means cummulative the data every time. i Have written many queries like that .... to execuate by passing the date parameters


    HTML Code:
    Sub CompData1(dAsOfDate As Date, dsEndDate As Date)
        Dim wSheet As Worksheet
        Dim wProd As Worksheet
        Dim dStartdate As Date, indate As Date
        Dim dEnddate As Date
        Dim sql As String
        Dim sPotRangeUL As String
        Dim dStartMonth As Date
        Dim dStartYear As Date
       
       dStartdate = CDate(dAsOfDate)
       dEnddate = CDate(dsEndDate)
          
       Dim sPotRangeUL1 As String
        Dim sPotRangeUL2 As String
        Dim sPotRangeUL3 As String
        Dim sPotRangeUL4 As String
        Dim sPotRangeUL5 As String
        Dim sPotRangeUL6 As String
        Dim sPotRangeUL7 As String
        Dim sPotRangeUL8 As String
        Dim sPotRangeUL9 As String
        Dim sPotRangeUL10 As String
        Dim sPotRangeUL11 As String
        Dim sPotRangeUL12 As String
        Dim sPotRangeUL13 As String
        Dim sPotRangeUL14 As String
        Dim sPotRangeUL15 As String
        Dim sPotRangeUL16 As String
        Dim sPotRangeUL17 As String
    
      
        sPotRangeUL = "A2"
        sPotRangeUL1 = "A3"
        sPotRangeUL2 = "A4"
        sPotRangeUL3 = "A5"
        sPotRangeUL4 = "A6"
        sPotRangeUL5 = "A7"
        sPotRangeUL6 = "A8"
        sPotRangeUL7 = "A9"
        sPotRangeUL8 = "A10"
        sPotRangeUL9 = "A11"
        sPotRangeUL10 = "A12"
        sPotRangeUL11 = "A13"
        sPotRangeUL12 = "A14"
        sPotRangeUL13 = "A15"
        sPotRangeUL14 = "A16"
        sPotRangeUL15 = "A17"
         sPotRangeUL16 = "A18"
          sPotRangeUL17 = "A19"
        '*******Get input data
      '  dStartDate = DateValue(dAsOfDate)
        'dStartDate = CDate("1-1-" + Str(Year(StartDate)))
       'dEndDate = DateValue(dsEndDate)
        dStartMonth = DateValue("1-" & Month(dStartdate) & "-" & Year(dStartdate))
        dStartYear = DateValue("1-jan" & "-" & Year(dStartdate))
        Set wSheet = Worksheets("test1")
    '    dEndDate = CDate(DateAdd("d", -1, DateValue(Str(Month(dStartDate) + 1) + "-1-" + Str(Year(dStartDate)))))
    
    'dEndDate = CDate(DateAdd("dd", -1, DateValue(Str(Month(EndDate) + 1) + "-1-" + Str(Year(EndDate)))))
         '*******Open Connection
        Call dataGetter.OpenConnection(sConnectStr)
           
     
    Dim Str_dStartdate As String
    Dim Str_dEnddate As String
    Str_dStartdate = Trim(Str(Year(dStartdate))) & IIf(Len(Month(dStartdate)) > 1, Trim(Str(Month(dStartdate))), "0" & Trim(Str(Month(dStartdate))))
    Str_dEnddate = Trim(Str(Year(dEnddate))) & IIf(Len(Month(dEnddate)) > 1, Trim(Str(Month(dEnddate))), "0" & Trim(Str(Month(dEnddate))))
    
    Str_dStartdate = Trim(Str(Year(dStartdate))) & Trim(Str(Month(dStartdate)))
    
    Str_dEnddate = Trim(Str(Year(dEnddate))) & Trim(Str(Month(dEnddate)))
    
    Str_dStartdate2 = Trim(Str(Year(dStartdate))) & IIf(Len(Month(dStartdate)) > 1, Trim(Str(Month(dStartdate))), "0" & Trim(Str(Month(dStartdate))))
    Str_dEnddate2 = Trim(Str(Year(dEnddate))) & IIf(Len(Month(dEnddate)) > 1, Trim(Str(Month(dEnddate))), "0" & Trim(Str(Month(dEnddate))))
    
    
    Dim Str_dStartdate1 As String
    Dim Str_dEnddate1 As String
    
    Str_dStartdate1 = IIf(Len(Month(dStartdate)) > 1, Trim(Str(Month(dStartdate))), "0" & Trim(Str(Month(dStartdate)))) & Trim(Str(Year(dStartdate)))
    Str_dEnddate1 = IIf(Len(Month(dEnddate)) > 1, Trim(Str(Month(dEnddate))), "0" & Trim(Str(Month(dEnddate)))) & Trim(Str(Year(dEnddate)))
    
          
    
        '---------------------------------------------Since Inception to month range----------------------------------------'
     sql = "   SELECT  CONVERT(VARCHAR,YEAR(OTR.START_DATETIME))+ ''+ CASE " & _
    "   WHEN (LEN(CONVERT(varchar, MONTH(OTR.[START_DATETIME])))>1 )" & _
    "           THEN CONVERT(varchar, MONTH(OTR.[START_DATETIME]))" & _
    "            Else '0' + CONVERT(varchar, MONTH(OTR.[START_DATETIME])) END AS MONTH_DATA, " & _
    "     ISNULL(SUM(OTR.[ACT_COND_VOL]),0)," & _
    "       (SELECT ISNULL(SUM(INR.[ACT_COND_VOL]),0)  FROM CUST_TOTALS INR" & _
    "        WHERE ITEM_NAME='STD'  AND CONVERT(INT,CONVERT(VARCHAR,YEAR(INR.START_DATETIME))+ ''+ CASE WHEN (LEN(CONVERT(varchar, MONTH(INR.[START_DATETIME])))>1 )" & _
    "    THEN CONVERT(varchar, MONTH(INR.[START_DATETIME]))Else '0' + CONVERT(varchar, MONTH(INR.[START_DATETIME])) END)" & _
    "    <=CONVERT(INT,CONVERT(VARCHAR,YEAR(OTR. START_DATETIME))+ ''+ CASE WHEN (LEN(CONVERT(varchar, MONTH(OTR. [START_DATETIME])))>1 )" & _
    "       THEN CONVERT(varchar, MONTH(OTR .[START_DATETIME]))Else '0' + CONVERT(varchar, MONTH(OTR .[START_DATETIME])) END))" & _
    "      FROM CUST_TOTALS OTR " & _
    "        WHERE ITEM_NAME='STD'  AND CONVERT(INT,CONVERT(VARCHAR,YEAR(OTR.START_DATETIME))+ ''+ CASE WHEN (LEN(CONVERT(varchar, MONTH(OTR.[START_DATETIME])))>1 )" & _
    "    THEN CONVERT(varchar, MONTH(OTR.[START_DATETIME]))Else '0' + CONVERT(varchar, MONTH(OTR.[START_DATETIME])) END)" & _
    "    >=" & Str_dStartdate2 & " AND " & _
    "    CONVERT(INT,CONVERT(VARCHAR,YEAR(OTR.START_DATETIME))+ ''+ CASE WHEN (LEN(CONVERT(varchar, MONTH(OTR.[START_DATETIME])))>1 )" & _
    "       THEN CONVERT(varchar, MONTH(OTR.[START_DATETIME]))Else '0' + CONVERT(varchar, MONTH(OTR.[START_DATETIME])) END)" & _
    "   <=" & Str_dEnddate2 & " " & _
    "           Group BY CONVERT(VARCHAR,YEAR(OTR.START_DATETIME))+ ''+ CASE " & _
    "   WHEN (LEN(CONVERT(varchar, MONTH(OTR.[START_DATETIME])))>1 )" & _
    "           THEN CONVERT(varchar, MONTH(OTR.[START_DATETIME]))" & _
    "            Else '0' + CONVERT(varchar, MONTH(OTR.[START_DATETIME])) END "
     Call dataGetter.GetOperFieldRead(dStartdate, dEnddate, sql, 60, sPotRangeUL, 0, 100)
        Set recReportData = Nothing
        Application.DisplayAlerts = True
        Call dataGetter.CloseConnection
        
            
    End Sub
    Last edited by farrukh; 2012-02-23 at 06:09.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Fundamentally, the query is slow because your SQL is slow.

    First, all that CONVERT stuff is unnecessarily complex if I understand your database correctly. Use CONVERT to change the datatime values to strings using style 112 and use substring to remove the day

    Next, use a different approach to get your third field, the sum(). Your method looks at all records in cust_totals and does a separate select for each record, counting records in cust_totals again. I doubt SQL can optimise that. If you instead do a self-join

    Code:
    select 
         x, 
         y, 
         sum(z) 
    from cust_totals inr 
         inner join cust_totals otr 
             on item_name = 'std'
             and itr.start_datetime <= otr.start_datetime
    group by...
    or something similar usng convert as above, I would expect a much faster result. (is removing the day actually necessary?)

    Finally, when asking for help it is nice if you do some leg work yourself first. Format that SQL statement sensibly with neat indenting and alignment so it is clear what it does. As it is it took me half an hour just to work out what it did!

    Ian.
    Last edited by iansavell; 2012-02-23 at 07:42.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 481 Times in 458 Posts
    Hi Farrukh

    Ian has given very good advice.
    Clearer and effective.
    If you follow his advice your query should be much faster.
    Please let us know.

    zeddy

  6. #6
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Ian,

    I am extemely aplogize that you have consumed a lot of your precious time to reading the query that was my fault i was in so much hurry because i am very new here to my first job really sorry.


    The idea you have given is fantastic i remove all the conversion to string and then re-test it works much faste 30+ minutes come to less then 4 mins rolleyes

    Zeddy thank you so much always giving quick respose may you all who are supporting people God bless you always ...

    Thanks
    farrukh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •