Should I prefilter my sql server statement or have power query

[shophoney]

I'm looking to speed up my Excel file with lots of data being pulled from our sql server system.

I've created statements like below and would like to know if I should pre filter the sales from 2015 and prior. Rather than having power query remove the unneeded rows and columns.

If so how would i add to the statement to remove sales prior to 01/01/2015?

SELECT tblsale.sale_link,
tblsale.loc_code AS [LOC#],
tblsale.DATE AS [TRANS DATE],
tblsale.TIME,
tblsale.trans_no AS [TRANS#],
tblsale.customer_code AS [CUST#],
tblsalecommission.salesperson,
tblsaleitem.sku_no AS [SKU#],
tblsaleitem.description,
Min(tblsalediscount.disc_code) AS [DISCOUNT CODE],
tblsaleitem.qty AS [UNITS SOLD],
SUM(tblsaleitem.ext_cost) AS COST,
[unit_ext_price] - [unit_ext_best_price] AS DISCOUNT,
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price AS [NET RETAIL],
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note AS [SALE NOTE],
tblsaleitem.note AS [SALE ITEM NOTE]
FROM tblsalediscount
RIGHT JOIN (tblsale
INNER JOIN (tblsaleitem
INNER JOIN tblsalecommission
ON ( tblsaleitem.line_no =
tblsalecommission.line_no )
AND ( tblsaleitem.sale_link =
tblsalecommission.sale_link ))
ON tblsale.sale_link = tblsaleitem.sale_link)
ON ( tblsalediscount.line_no = tblsaleitem.line_no )
AND ( tblsalediscount.sale_link = tblsaleitem.sale_link )
GROUP BY tblsale.sale_link,
tblsale.loc_code,
tblsale.DATE,
tblsale.TIME,
tblsale.trans_no,
tblsale.customer_code,
tblsalecommission.salesperson,
tblsaleitem.sku_no,
tblsaleitem.description,
tblsaleitem.qty,
[unit_ext_price] - [unit_ext_best_price],
tblsaleitem.unit_ext_price,
tblsaleitem.unit_ext_best_price,
tblsaleitem.line_no,
tblsale.trans_type,
tblsale.void_code,
tblsale.suspended,
tblsaleitem.affect_inv,
tblsaleitem.affect_total,
tblsale.note,
tblsaleitem.note;

 

[gazpage]

Push as much as possible to the server, but PQ will do it for you via query folding. I wouldn't write your own SQL unless you are a SQL wizard and are sure your method is more performant.

 

[shophoney]

Push as much as possible to the server, but PQ will do it for you via query folding. I wouldn't write your own SQL unless you are a SQL wizard and are sure your method is more performant.

Hi all my queries have that option grayed out.

What i did was write the queries in ACCESS and convert the to SQL then filter dates and info not needed in excel using power query.

I need all the tables as it has SALES, RECEIVINGS, TRANSFERS, ON HAND, PRODUCT INFO, EMPLOYEES...

But i need to figure out how to pick up speed. Maybe if I modify the above query to exclude dates prior to 1/1/15 in SALES, TRANSFERS, RECEIVINGS.. might help a bit.

I have a super fast an new computer.

 

[akice]

Push as much as possible to the server, but PQ will do it for you via query folding. I wouldn't write your own SQL unless you are a SQL wizard and are sure your method is more performant.

That pretty much says it all but for one more point. With Power Query to take advantage of query folding, be sure to do row filtering steps before you do other transformations. If you don't you could end up pulling a lot more records across the wire which get discarded in a later step.

 

[shophoney]

That pretty much says it all but for one more point. With Power Query to take advantage of query folding, be sure to do row filtering steps before you do other transformations. If you don't you could end up pulling a lot more records across the wire which get discarded in a later step.

Any thoughts why this SQL Query wouldn't work. I've tried to remove old sales.

SELECT tbljournal.posted_dt AS [JOURNAL DATE],
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost AS [EXT LANDED COST],
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note
FROM (tblreceiveitem
INNER JOIN tblreceive
ON ( tblreceiveitem.source_loc_code =
tblreceive.source_loc_code )
AND ( tblreceiveitem.dest_loc_code =
tblreceive.dest_loc_code )
AND ( tblreceiveitem.vendor_code =
tblreceive.vendor_code )
AND ( tblreceiveitem.reference_no =
tblreceive.reference_no ))
INNER JOIN tbljournal
ON tblreceive.link_journal = tbljournal.link_journal
GROUP BY tbljournal.posted_dt,
tblreceive.link_journal,
tblreceive.dest_loc_code,
tblreceive.reference_no,
tblreceive.vendor_code,
tblreceive.transact_type,
tblreceiveitem.sku_no,
tblreceiveitem.qty,
tblreceiveitem.cost,
tblreceiveitem.landed_cost,
tblreceive.rebate_perc,
tblreceiveitem.rebate_amount,
tblreceive.currency_code,
tblreceive.exchange_rate,
tblreceive.gst,
tblreceiveitem.retail,
tblreceive.note,
tblreceiveitem.line_no
HAVING (( ( tbljournal.posted_dt ) ># 1 / 31 / 2015# ));

 

[gazpage]

I can't promise it will be quicker, but if I were in your shoes I would try dropping the sql Query altogether and recreate it in Power Query. See if that is quicker.

 

[shophoney]

Hi. Not sure if I fully understand.

I converted a ms access sql statement to a power query. I've created the connection and then pasted in the sql server statement selecting the tables and data to display. Is there a better way to draw the data from our database?

Thanks

 

[akice]

Hi. Not sure if I fully understand.

I converted a ms access sql statement to a power query. I've created the connection and then pasted in the sql server statement selecting the tables and data to display. Is there a better way to draw the data from our database?

Thanks

What gazpage is saying is use the Power Query connector to connect to the data source and use the Power Query UI to filter/transform/merge the tables. The connector will generate the SQL statements to pull the data. So put another way, gazpage is saying don't write any SQL at all. Let Power Query handle it.

 

[shophoney]

What gazpage is saying is use the Power Query connector to connect to the data source and use the Power Query UI to filter/transform/merge the tables. The connector will generate the SQL statements to pull the data. So put another way, gazpage is saying don't write any SQL at all. Let Power Query handle it.

I feel stupid not fully understanding all of this.

But am I correct it thinking that I should pull tblSALE, tblSALEITEM, and tblSALEDISCOUNT all into power query? Should they all be separate and linked by common fields or merged as on big table with all columns?

It will a lot of data either way, as i need 3 years worth of sales.

The refresh takes to long and my system is powerful.

What i did was write a Access qry as i know how to do that, and then converted it to a SQL Server (through an online site).

In a perfect world what is the best and am I going about it?

 

[akice]

But am I correct it thinking that I should pull tblSALE, tblSALEITEM, and tblSALEDISCOUNT all into power query? Should they all be separate and linked by common fields or merged as on big table with all columns?

It will a lot of data either way, as i need 3 years worth of sales.

From what it looks like, you will probably use Power Query pull in the 3 tables to the data model separately, then link together in data model.

How many rows/columns of data do you have? The data model compresses things on a column basis. So the more columns you have and the more unique values in the columns along with number of records is of course going to determine file size and performance. So a rule of thumb is to only pull in the columns you need to data model.