Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
speed up refresh in excel
- Thread starter ajdesigns
- Start date
provide more info and we might be able to help - we ain't mind readers here ;-)
It will still depend though, more on the speed of your connection to the database being queried and the RAM on your computer than anything else but there may be inefficiencies in your query or forumla that we may be able to help with....but only if you post them !
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
It will still depend though, more on the speed of your connection to the database being queried and the RAM on your computer than anything else but there may be inefficiencies in your query or forumla that we may be able to help with....but only if you post them !
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
SkipVought
Programmer
Geoff,
I'm shattered! Here all along, I actually thot you could read minds!
First it was the tooth fairy, then the Easter Bunny, then Sant Clause...
and......now........xlbo.........ahhhhhhhaaaaaaahhhhhhhhhhgggggggggg!![[cry]](/data/assets/smilies/cry.gif "[cry] [cry]")
Skip,
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
I'm shattered! Here all along, I actually thot you could read minds!
First it was the tooth fairy, then the Easter Bunny, then Sant Clause...
and......now........xlbo.........ahhhhhhhaaaaaaahhhhhhhhhhgggggggggg!
Skip,
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...Neopolitan Blownapart!
![[rofl]](/data/assets/smilies/rofl.gif "[rofl] [rofl]")
My MindReader Toolpaktm is on the fritz - I'm back to actually having to see stuff to know what's wrong with it :-(
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
SkipVought
Programmer
Ahhhhhhhh, an Add-In that you've been hiding from the REST of us. Damn!
Skip,
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...Neopolitan Blownapart!
- Thread starter
- #6
Hi
Sorry about that
This is a SQL view of my Query.
SELECT DISTINCT stockm.analysis_b, month(invoice_date), year(invoice_date), LEFT(opheadm.order_no,1), LEFT(stockm.analysis_b,3), Sum(opdetm.list_price*opdetm.despatched_qty/ceratem.exchange_rate), Sum(opdetm.despatched_qty), Sum((despatched_qty)*standard_cost), opheadm.territory
FROM b_live.scheme.ceratem ceratem, b_live.scheme.opdetm opdetm, b_live.scheme.opheadm opheadm, b_live.scheme.slcustm slcustm, b_live.scheme.stockm stockm
WHERE opheadm.customer = slcustm.customer AND opdetm.order_no = opheadm.order_no AND slcustm.currency = ceratem.currency_code AND stockm.product = opdetm.product AND stockm.warehouse = opdetm.warehouse AND ((opdetm.order_no Not Like '5%') AND (ceratem.currency_type='YR') AND (opheadm.invoice_date>={ts '2005-01-01 00:00:00'}) AND (ceratem.currency_year='05'))
GROUP BY stockm.analysis_b, month(invoice_date), year(invoice_date), LEFT(opheadm.order_no,1), LEFT(stockm.analysis_b,3), opheadm.territory
ORDER BY stockm.analysis_b
this is a sample of the data it returns:
Where product grp month, year are self explanitory. you then get CN = credit , if 0 its a sale and adds it , if C its credit and deducts it, Main equals the customer segment ,Total = despatched qty * list price /exchange rate, Total Qty = despatched qty, COS =despatched qty * standard cost, No heading = Territory .there is then a key=month,year,product group,Territory,credit.
Productgroup Month Year CN Main Total Total Qty C O S Key
LTSIMM 1 2005 0 LTS 35895 389 15360.70 1220 12005LTSIMM12200
LTSIMM 1 2005 0 LTS 30757 320 17143.43 353 12005LTSIMM3530
LTSIMM 1 2005 0 LTS 3150 30 1180.27 D009 12005LTSIMMD0090
LTSIMM 1 2005 C LTS 2050 24 925.83 1220 12005LTSIMM1220C
LTSIMM 2 2005 0 LTS 10115 141 6450.93 1220 22005LTSIMM12200
LTSIMM 2 2005 0 LTS 16035 179 10518.66 353 22005LTSIMM3530
LTSIMM 2 2005 0 LTS 6580 55 2462.76 D009 22005LTSIMMD0090
LTSIMM 3 2005 0 LTS 9789 151 6122.80 1220 32005LTSIMM12200
LTSIMM 3 2005 0 LTS 23470 252 13196.00 353 32005LTSIMM3530
LTSIMM 4 2005 0 LTS 1992 27 1229.46 1220 42005LTSIMM12200
LTSIMM 4 2005 0 LTS 2691 29 906.32 353 42005LTSIMM3530
LTSSTN 1 2005 0 LTS 33642 1565 32526.65 1220 12005LTSSTN12200
LTSSTN 2 2005 0 LTS 15263 710 14756.50 1220 22005LTSSTN12200
LTSSTN 3 2005 0 LTS 9351 435 9040.95 1220 32005LTSSTN12200
NPTBLX 4 2005 0 NPT 1194 239 1529.65 353 42005NPTBLX3530
NPTCKMB 1 2005 0 NPT 8500 1368 3921.29 1220 12005NPTCKMB12200
NPTCKMB 1 2005 0 NPT 9333 4004 10464.45 353 12005NPTCKMB3530
NPTCKMB 1 2005 0 NPT 2891 483 1215.28 381 12005NPTCKMB3810
NPTCKMB 1 2005 0 NPT 979 168 439.07 D404 12005NPTCKMBD4040
NPTCKMB 1 2005 0 NPT 1260 216 670.27 D412 12005NPTCKMBD4120
NPTCKMB 1 2005 0 NPT 420 72 188.17 D512 12005NPTCKMBD5120
NPTCKMB 1 2005 0 NPT 280 48 148.95 D528 12005NPTCKMBD5280
NPTCKMB 1 2005 0 NPT 840 144 392.60 D664 12005NPTCKMBD6640
NPTCKMB 1 2005 0 NPT 3357 532 1586.53 D732 12005NPTCKMBD7320
I then have a front end with a VLookup
=IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))+IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20&"C",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20&"C",TERRITORY,2,FALSE))
Where H6=month, H4= Year ,A7 = prod grp , A20 = territory
so
The lookup looks at the frontend picks up month, year, prod grp, territory, and whether its a credit or debit goes to the data returned , then returns the total sales for the month.
This example is only 1 prod group/ territory combo alltogether there are 29 prod grps and 18 territories
I hope this explains what I am doing
AJD
Sorry about that
This is a SQL view of my Query.
SELECT DISTINCT stockm.analysis_b, month(invoice_date), year(invoice_date), LEFT(opheadm.order_no,1), LEFT(stockm.analysis_b,3), Sum(opdetm.list_price*opdetm.despatched_qty/ceratem.exchange_rate), Sum(opdetm.despatched_qty), Sum((despatched_qty)*standard_cost), opheadm.territory
FROM b_live.scheme.ceratem ceratem, b_live.scheme.opdetm opdetm, b_live.scheme.opheadm opheadm, b_live.scheme.slcustm slcustm, b_live.scheme.stockm stockm
WHERE opheadm.customer = slcustm.customer AND opdetm.order_no = opheadm.order_no AND slcustm.currency = ceratem.currency_code AND stockm.product = opdetm.product AND stockm.warehouse = opdetm.warehouse AND ((opdetm.order_no Not Like '5%') AND (ceratem.currency_type='YR') AND (opheadm.invoice_date>={ts '2005-01-01 00:00:00'}) AND (ceratem.currency_year='05'))
GROUP BY stockm.analysis_b, month(invoice_date), year(invoice_date), LEFT(opheadm.order_no,1), LEFT(stockm.analysis_b,3), opheadm.territory
ORDER BY stockm.analysis_b
this is a sample of the data it returns:
Where product grp month, year are self explanitory. you then get CN = credit , if 0 its a sale and adds it , if C its credit and deducts it, Main equals the customer segment ,Total = despatched qty * list price /exchange rate, Total Qty = despatched qty, COS =despatched qty * standard cost, No heading = Territory .there is then a key=month,year,product group,Territory,credit.
Productgroup Month Year CN Main Total Total Qty C O S Key
LTSIMM 1 2005 0 LTS 35895 389 15360.70 1220 12005LTSIMM12200
LTSIMM 1 2005 0 LTS 30757 320 17143.43 353 12005LTSIMM3530
LTSIMM 1 2005 0 LTS 3150 30 1180.27 D009 12005LTSIMMD0090
LTSIMM 1 2005 C LTS 2050 24 925.83 1220 12005LTSIMM1220C
LTSIMM 2 2005 0 LTS 10115 141 6450.93 1220 22005LTSIMM12200
LTSIMM 2 2005 0 LTS 16035 179 10518.66 353 22005LTSIMM3530
LTSIMM 2 2005 0 LTS 6580 55 2462.76 D009 22005LTSIMMD0090
LTSIMM 3 2005 0 LTS 9789 151 6122.80 1220 32005LTSIMM12200
LTSIMM 3 2005 0 LTS 23470 252 13196.00 353 32005LTSIMM3530
LTSIMM 4 2005 0 LTS 1992 27 1229.46 1220 42005LTSIMM12200
LTSIMM 4 2005 0 LTS 2691 29 906.32 353 42005LTSIMM3530
LTSSTN 1 2005 0 LTS 33642 1565 32526.65 1220 12005LTSSTN12200
LTSSTN 2 2005 0 LTS 15263 710 14756.50 1220 22005LTSSTN12200
LTSSTN 3 2005 0 LTS 9351 435 9040.95 1220 32005LTSSTN12200
NPTBLX 4 2005 0 NPT 1194 239 1529.65 353 42005NPTBLX3530
NPTCKMB 1 2005 0 NPT 8500 1368 3921.29 1220 12005NPTCKMB12200
NPTCKMB 1 2005 0 NPT 9333 4004 10464.45 353 12005NPTCKMB3530
NPTCKMB 1 2005 0 NPT 2891 483 1215.28 381 12005NPTCKMB3810
NPTCKMB 1 2005 0 NPT 979 168 439.07 D404 12005NPTCKMBD4040
NPTCKMB 1 2005 0 NPT 1260 216 670.27 D412 12005NPTCKMBD4120
NPTCKMB 1 2005 0 NPT 420 72 188.17 D512 12005NPTCKMBD5120
NPTCKMB 1 2005 0 NPT 280 48 148.95 D528 12005NPTCKMBD5280
NPTCKMB 1 2005 0 NPT 840 144 392.60 D664 12005NPTCKMBD6640
NPTCKMB 1 2005 0 NPT 3357 532 1586.53 D732 12005NPTCKMBD7320
I then have a front end with a VLookup
=IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20&"0",TERRITORY,2,FALSE))+IF(ISERROR(VLOOKUP(H$6&$H$4&$A7&$A$20&"C",TERRITORY,2,FALSE))=TRUE,0,VLOOKUP(H$6&$H$4&$A7&$A$20&"C",TERRITORY,2,FALSE))
Where H6=month, H4= Year ,A7 = prod grp , A20 = territory
so
The lookup looks at the frontend picks up month, year, prod grp, territory, and whether its a credit or debit goes to the data returned , then returns the total sales for the month.
This example is only 1 prod group/ territory combo alltogether there are 29 prod grps and 18 territories
I hope this explains what I am doing
AJD
- Thread starter
- #7
Ok - not a total whiz on SQL but I don't really see much scope for making the query more efficient
In terms of the VLOOKUPS, how many of them do you have ?? (ie how many cells have this vlookup formula in them). Vlookups are notoriously slow to calc if there are a lot of 'em.
Last point is the RAM - 256 is ok but it ain't massive. You may well see an improvement by upping the RAM to 512. Other than that, if the query is running off a big table with lots of records then its gonna be slow whatever you do - may be a case to ask the DBA to design you a view that holds this data at a more consolidated level...
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
In terms of the VLOOKUPS, how many of them do you have ?? (ie how many cells have this vlookup formula in them). Vlookups are notoriously slow to calc if there are a lot of 'em.
Last point is the RAM - 256 is ok but it ain't massive. You may well see an improvement by upping the RAM to 512. Other than that, if the query is running off a big table with lots of records then its gonna be slow whatever you do - may be a case to ask the DBA to design you a view that holds this data at a more consolidated level...
Rgds, Geoff
"Three things are certain: Death, taxes and lost data. Guess which has occurred"
Please read FAQ222-2244 before you ask a question
SkipVought
Programmer
try playing around with the WHERE clause to tune it, so that the MOST narrowing criteriae are stated first...
Skip,
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!
Code:
...
WHERE ((opdetm.order_no Not Like '5%')
AND (ceratem.currency_type='YR')
AND (opheadm.invoice_date>={ts '2005-01-01 00:00:00'})
AND (ceratem.currency_year='05'))
AND opheadm.customer = slcustm.customer
AND opdetm.order_no = opheadm.order_no
AND slcustm.currency = ceratem.currency_code
AND stockm.product = opdetm.product
AND stockm.warehouse = opdetm.warehouse
...Skip,
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...Neopolitan Blownapart!
- Thread starter
- #10
If the backend server accepts the more modern syntax of
FROM TABLEA
JOIN TABLEB ON CONDITION1
JOIN TABLEC ON CONDITION2
...
this is likely to be faster.
Putting the join conditions in the WHERE clause may cause the server to do a cross join of all tables and then filter down and that can involve handling an order of magnitude more rows. Certainly the syntax that has been used makes it harder for a query optimiser to optimise.
FROM TABLEA
JOIN TABLEB ON CONDITION1
JOIN TABLEC ON CONDITION2
...
this is likely to be faster.
Putting the join conditions in the WHERE clause may cause the server to do a cross join of all tables and then filter down and that can involve handling an order of magnitude more rows. Certainly the syntax that has been used makes it harder for a query optimiser to optimise.
- Status