Is it possible to alter my SQL code to make my SSRS report run faster?

I'm new to SSRS reports and trying to speed up the run time of my SSRS report. If I add further filters (in the WHERE clause), I can get it to run. However; as is, the report runs for a while and then times out. The data I need is in three different tables.

Here is my code:

` `SELECT DISTINCT
  gl_detail.a_org
  ,gl_detail.a_object
  ,gl_master.a_object_desc
  ,ap_invoice.i_gl_eff_date
  ,gl_detail.j_jnl_source
  ,ap_invoice.a_vendor_number
  ,ap_vendor.a_vendor_name
  ,ap_invoice.a_purch_order_no
  ,ap_invoice.i_invoice_desc
  ,ap_invoice.a_check_number
  ,ap_invoice.a_line_item_amount
  ,ap_invoice.j_jrnl_entry_year
  ,ap_invoice.j_jrnl_entry_per

FROM
  gl_detail
  INNER JOIN ap_invoice
    ON gl_detail.a_object = ap_invoice.a_object
  INNER JOIN ap_vendor
    ON ap_invoice.a_vendor_number = ap_vendor.a_vendor_number
  INNER JOIN gl_master
    ON ap_invoice.a_object = gl_master.a_object
WHERE
  gl_detail.j_jnl_source = N'API'
  AND gl_detail.a_org = N'10051401'  
  AND gl_detail.a_object >= N'50000'
  AND gl_detail.a_object < N'60000'
  AND ap_invoice.j_jrnl_entry_year = N'2023'

ORDER BY
   gl_master.a_object_desc
  ,ap_invoice.j_jrnl_entry_year
  ,gl_detail.a_org
  ,gl_detail.a_object
  ,ap_invoice.i_gl_eff_date
  ,gl_detail.j_jnl_source
  ,ap_invoice.a_vendor_number
  ,ap_vendor.a_vendor_name
  ,ap_invoice.a_purch_order_no
  ,ap_invoice.i_invoice_desc
  ,ap_invoice.a_check_number
  ,ap_invoice.a_line_item_amount
  ,ap_invoice.j_jrnl_entry_per

I read a lot of details about similar problems, but either the solutions didn't work for me, or I'm not skilled enough to implement them. I tried using different tables that had the same data to link, but none of them sped up the report. At a minimum, I would still need to link three different tables to link this data. I also tried linking different variables, but either the data came back incorrect or null. I added the "order by" clause, I tried adding "declare" to declare variables but kept receiving errors. I found information on the SQL Profiler and SQL Server Management Studio but I don't have access to those at my company and we can't download new software onto our computers.

I added three additional items in my "WHERE" clause just to test that the report would actually run. It took about sixty seconds to return one line of data. However, I do not want those filters in the final report. See screenshot

Initially wanted to run it for multiple years and add a drop down parameter to select the year at run time, but I removed that and changed it to adding the '2023' date in the WHERE clause to narrow down the data.

Thank you.

Not an answer, just a guide for troubleshooting.

Given this comment: I believe I need the "DISTINCT" component, otherwise it gives me many lines of duplicate data

Likely your joins are incorrect. Your joins are making it do too much work by joining too many rows, then you make it do extra work after on that with distinct. If you can get the join columns correct and remove distinct you'll have a large performance improvement

To troubleshoot your joins you need to open up SSMS (Assuming this is SQL Server) and run this to count the filtered rows in gl_detail

SELECT COUNT(*) As RowsFoundd
FROM
  gl_detail
WHERE
  gl_detail.j_jnl_source = N'API'
  AND gl_detail.a_org = N'10051401'  
  AND gl_detail.a_object >= N'50000'
  AND gl_detail.a_object < N'60000'

Now put your first join in. You should get the same number of rows.

SELECT COUNT(*) As RowsFound
FROM
  gl_detail
  INNER JOIN ap_invoice
    ON gl_detail.a_object = ap_invoice.a_object
WHERE
  gl_detail.j_jnl_source = N'API'
  AND gl_detail.a_org = N'10051401'  
  AND gl_detail.a_object >= N'50000'
  AND gl_detail.a_object < N'60000'

If you get a more rows, then this join is incorrect. You likely need to add more columns to this join

ON gl_detail.a_object = ap_invoice.a_object

If you get the same number of rows, keep adding joins until you work out what join is generating duplicates.

Also keep in mind just running distinct on this can drop out legitimate records. Your report is not going to reconcile.

This is not an answer as much but too much info for a comment..

First - seanb's comments are all valid
Second - I'm assuming this is SQL Server as you have not specified

You would need to share more details. Share the table schemas and indexes and the approximate row counts for each. To get the table and indexes scripted out, right-click then and then do "Script Table As >> Create To >> New query window" and do a similar task for the indexes.

A few comments on the code.

In your WHERE clause you are comparing to unicode strings even for columns that appear to be numeric.

WHERE
  gl_detail.j_jnl_source = N'API'
  AND gl_detail.a_org = N'10051401'  
  AND gl_detail.a_object >= N'50000'
  AND gl_detail.a_object < N'60000'
  AND ap_invoice.j_jrnl_entry_year = N'2023'

If the last four columns here are numeric then changing it to this

WHERE
  gl_detail.j_jnl_source = N'API'
  AND gl_detail.a_org = 10051401
  AND gl_detail.a_object >= 50000
  AND gl_detail.a_object < 60000
  AND ap_invoice.j_jrnl_entry_year = 2023

would help as currently the query is having to take the unicode string e.g. N'2023' and implicitly convert it to a number in order to compare it to the j_jrnl_entry_year column in the table.

Next, the DISTINCT is probably a bad idea here, if you have two identical records with the same amounts, then only one would be returned which I assume would be wrong. This will also slow things down a little.

Next, if this is being used as a dataset query, then there is no need for the ORDER clause, the data will be ordered in the report based on your rowgroups and sorting so it's just a waste of time putting it in the query.

Next, using temp tables can often increase performance when you are joining larges tables (maybe they are not large but this is worth trying) and applying the WHERE clause to more than one table.

Below I've restructured the query including all the above changes. Basically what I have done is grabbed the data from the ap_invoice table just for the year you need, put the results into a temp table and then joined to this rather than the original table. You could do a simialr task on thegl_detail table too but I'll leave that to you. This may not give any performance increase, but it often does in my experience.

(I've also aliased the table names below as it makes the whole thing easier to read)

-- create a temp table called #inv containing only the ap_invoive data we are interested in.
SELECT * INTO #inv FROM ap_invoice WHERE j_jrnl_entry_year = 2023

SELECT 
  gl.a_org
  ,gl.a_object
  ,m.a_object_desc
  ,i.i_gl_eff_date
  ,gl.j_jnl_source
  ,i.a_vendor_number
  ,v.a_vendor_name
  ,i.a_purch_order_no
  ,i.i_invoice_desc
  ,i.a_check_number
  ,i.a_line_item_amount
  ,i.j_jrnl_entry_year
  ,i.j_jrnl_entry_per
FROM gl_detail AS gl
  INNER JOIN #inv AS i -- use the new, smaller invoice table created above
    ON gl.a_object = o.a_object
  INNER JOIN ap_vendor AS v
    ON o.a_vendor_number = v.a_vendor_number
  INNER JOIN gl_master AS m
    ON i.a_object = m.a_object
WHERE
  gl.j_jnl_source = N'API'
  AND gl.a_org = 10051401
  AND gl.a_object >= 50000
  AND gl.a_object < 60000

By selecting data from different tables and joining them properly, I was able to get the data that I need for my report to run almost instantly.I added a parameter to select the year at run time.

SELECT DISTINCT
  gl_history.a_org
  ,gl_history.a_object
  ,gl_history.h_effective_date
  ,gl_history.j_jnl_source
  ,gl_history.h_ref1_vendor
  ,gl_history.h_ref2_po_no
  ,gl_history.h_line_comment
  ,gl_history.h_gross_amount
  ,gl_history.h_transact_year
  ,ap_invoice.a_invoice_number
  ,gl_master.a_object_desc
  ,ap_invoice.a_check_number
  ,ap_vendor.a_vendor_name
  ,gl_history.j_jnl_line_id 
FROM
  gl_history
  INNER JOIN ap_invoice
    ON gl_history.h_ref3_invoice_no = ap_invoice.a_invoice_number AND gl_history.a_org = ap_invoice.a_org
  INNER JOIN ap_vendor
    ON ap_invoice.a_vendor_number = ap_vendor.a_vendor_number
  INNER JOIN gl_master
    ON gl_master.a_object = gl_history.a_object AND gl_master.a_org = gl_history.a_org
WHERE
  gl_history.a_org = N'10051401'
  AND gl_history.a_object >= N'50000'
  AND gl_history.a_object < N'60000'
  AND gl_history.j_jnl_source = N'API'