Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2016
    Posts
    2

    Unanswered: Query Performance

    Basically what I did was run a section of the input build, the part that rolls up the quantity 1 table to similar records and did timings as I added more streams. Each stream is roughly 500K records, give or take a few records. Following the path of stream 1 you will notice that each stream that is added the time to completion for stream 1 increases. For the most part the increases are right around 30-40 secs. This basically means that a set of queries that took 2 minutes to run by itself will now take 25+ minutes to run at 50 streams. The last two runs I increased by 5 streams at a time and the pattern is fairly similar, 3 minute jump for 5 streams which averages down to around 40 second increase per stream.

    In theory our database shouldn’t have a linear increase when adding streams, it should be more of an exponential curve. You have a small increase, minimal time jump maybe a few seconds, each time you add a new stream/query in parallel then there should be a theoretical maximum where you see a large jump in query time.

    This should tell us that at X number of streams we will have the best performance, but anything over X and we are losing performance. In our model, technically anything over a single stream causes worse performance because you have such large jumps each time a stream is added.

    Please review the timings below. Hope all of this makes sense and someone can either validate the timings below are accurate or recommend some changes to allow us to run multiple queries in parallel.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	2 
Size:	29.5 KB 
ID:	16817


    3 3 9:08:29 9:10:36 0:02:07
    3 4 9:28:08 9:30:05 0:01:57
    3 5 9:32:54 9:36:03 0:03:09
    3 6 9:43:51 9:47:20 0:03:29
    3 7 9:49:16 9:53:10 0:03:54
    3 8 9:57:43 10:02:01 0:04:18
    3 9 10:04:30 10:09:57 0:05:27
    3 10 10:15:44 10:21:30 0:05:46
    3 11 10:32:07 10:38:29 0:06:22
    3 12 10:40:33 10:47:14 0:06:41
    3 13 10:50:02 10:57:24 0:07:22
    3 14 11:08:38 11:16:29 0:07:51
    3 15 11:24:26 11:33:15 0:08:49
    3 16 12:50:55 13:00:22 0:09:27
    3 17 13:20:17 13:30:00 0:09:43
    3 18 13:39:29 13:49:43 0:10:14
    3 19 15:29:46 15:40:46 0:11:00
    3 20 8:07:31 8:20:08 0:12:37
    3 25 12:32:22 12:47:05 0:14:43
    3 30 11:39:10 11:57:00 0:17:50

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you really give us no useful info to be able to help you in any way. so, the more streams of data you attempt to process your elapsed time per stream increases. This is normal. The amount of time it increases has to do with a ton of other info. How constrained is your box? How poorly performing is your application? are you having a ton of lock waits as you add more streams? Are you swimming up stream in your streams? etc.etc.etc...
    Dave

  3. #3
    Join Date
    Mar 2016
    Posts
    2

    Query included

    Quote Originally Posted by dav1mo View Post
    you really give us no useful info to be able to help you in any way. so, the more streams of data you attempt to process your elapsed time per stream increases. This is normal. The amount of time it increases has to do with a ton of other info. How constrained is your box? How poorly performing is your application? are you having a ton of lock waits as you add more streams? Are you swimming up stream in your streams? etc.etc.etc...


    I sent an attachment on the last post. did you see? still no value? the query in question is below. let me know what you think.

    update tableaprerollup
    set InstalledProductId = null
    ;
    set session group_concat_max_len = 10000000
    ;
    truncate tablea
    ;
    alter table tablea add IDList longtext
    ;
    create FULLTEXT INDEX IDList on tablea(IDList)
    ;
    drop table if exists tl
    ;
    create table tl as
    Select InstalledProductId,
    Lease_Cust_id
    from tablel
    where InstalledProductId is not null
    group by InstalledProductId
    ;
    create index InstalledProductId on tl(InstalledProductId)
    ;
    create index Lease_Cust_id on tl(Lease_Cust_id)
    ;
    insert into tablea (LastModifiedBy, LastModifiedTimestamp, LastModifiedUsing, Quantity, InstalledAtSiteID, InstalledAtCustomerName, InstalledAtCustomerCity, InstalledAtCustomerState, InstalledAtCustomerZip, InstalledAtCustomerCountry, InstalledAtCustomerAddress, ContractId, ShipDate, SerialNumber, CustomerId, ProductId, BusinessUnitId, ServiceLevelId, ConfiguredBTU, ConfiguredWatts, ConfiguredCubicFeet, ConfiguredSPI, CurrentServiceAmount, ServiceAmountFor12Months, ServiceAmountFor24Months, ServiceAmountFor36Months, GenericData, GenericComment, CurrentSoftwareAmount, SoftwareAmountFor12Months, SoftwareAmountFor24Months, SoftwareAmountFor36Months, MDF, DiscountCategory, ResellerAMID, ResellerName, SourceDate, ContractStart, ContractEnd, ContactName, ContactPhone, ContactEmail, ContactURL, AMPID, OrderingContactName, OrderingContactPhone, OrderingContactEmail, DNB_Dmst_Ultm_DUNS_No, DNB_Global_Ultm_DUNS_No, DNB_HQ_DUNS_No, DNB_Parnt_DUNS_No, DNB_Site_DUNS_No, DataSource, LeaseContractNumber, LeaseStart, LeaseEnd, LeaseType, LeaseTotalDuration, LeaseLapsedDuration, LeaseRemainingDuration, LeaseTotalAmount, LeaseMonthlyPayment, LeaseLapsedAmount, LeaseRemainingAmount, LeaseIsActive, EOSLDate, LeaseTotalAmountAllItems, GroupType, Channel, AgeBand, IDList)
    Select
    USER(),
    NOW(),
    'HP Data Builder',
    SUM(Quantity),
    InstalledAtSiteID,
    InstalledAtCustomerName,
    InstalledAtCustomerCity,
    InstalledAtCustomerState,
    InstalledAtCustomerZip,
    InstalledAtCustomerCountry,
    InstalledAtCustomerAddress,
    ContractId,
    DATE_ADD(MIN(ShipDate), INTERVAL (DATEDIFF(MAX(ShipDate), MIN(ShipDate))/2) DAY),
    trim(', ' from group_concat(distinct SerialNumber separator ', ')),
    CustomerId,
    ProductId,
    ip.BusinessUnitId,
    ServiceLevelId,
    SUM(p.BTU),
    SUM(p.Watts),
    SUM(p.CubicFeet),
    SUM(p.PowerUsage),
    SUM(CurrentServiceAmount),
    SUM(ServiceAmountFor12Months),
    SUM(ServiceAmountFor24Months),
    SUM(ServiceAmountFor36Months),
    GenericData,
    GenericComment,
    SUM(CurrentSoftwareAmount),
    SUM(SoftwareAmountFor12Months),
    SUM(SoftwareAmountFor24Months),
    SUM(SoftwareAmountFor36Months),
    MDF,
    DiscountCategory,
    ResellerAMID,
    ResellerName,
    CURDATE(),
    ContractStart,
    ContractEnd,
    ContactName,
    ContactPhone,
    ContactEmail,
    ContactURL,
    AMPID,
    OrderingContactName,
    OrderingContactPhone,
    OrderingContactEmail,
    DNB_Dmst_Ultm_DUNS_No,
    DNB_Global_Ultm_DUNS_No,
    DNB_HQ_DUNS_No,
    DNB_Parnt_DUNS_No,
    DNB_Site_DUNS_No,
    DataSource,
    LeaseContractNumber,
    DATE_ADD(MIN(LeaseStart), INTERVAL (DATEDIFF(MAX(LeaseStart), MIN(LeaseStart))/2) DAY),
    DATE_ADD(MIN(LeaseEnd), INTERVAL (DATEDIFF(MAX(LeaseEnd), MIN(LeaseEnd))/2) DAY),
    LeaseType,
    LeaseTotalDuration,
    LeaseLapsedDuration,
    LeaseRemainingDuration,
    SUM(LeaseTotalAmount),
    SUM(LeaseMonthlyPayment),
    SUM(LeaseLapsedAmount),
    SUM(LeaseRemainingAmount),
    MAX(LeaseIsActive),
    MIN(ip.EOSLDate),
    LeaseTotalAmountAllItems,
    GroupType,
    MAX(Channel),
    l.Lease_Cust_Id,
    trim(',' from group_concat(distinct ip.Id separator ','))
    FROM tableaprerollup as ip
    INNER JOIN tableb as p on ip.ProductId = p.Id
    LEFT JOIN tl as l on ip.Id = l.InstalledProductId
    where ip.ObligationType IN ('L')
    GROUP BY ip.BusinessUnitId, ip.CustomerId, p.ShortNameId, LeaseContractNumber, l.Lease_Cust_Id
    ;
    drop table if exists rollupCompare
    ;
    create table rollupcompare as
    Select Id as InstalledProductId, IDList as InstalledProductPreRollupId
    from tablea
    where Quantity = 1
    ;
    alter table rollupcompare modify InstalledProductPreROllupId int
    ;
    create index ID on rollupcompare(InstalledProductPreRollupId)
    ;
    call NC_Rollup()
    ;
    update tableaprerollup as a, rollupcompare as b
    set a.InstalledProductId = b.InstalledProductId
    where a.Id = b.InstalledProductPreRollupId
    ;
    alter table tablea
    drop column IDList
    ;

Posting Permissions

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