can somebody help my optimise my code please

Hi

I have the following SQL, which I am using to query our mainframe (some kind of IBM DB2 type thing)
Basically there are two tables joined by account number. One contains info about the customer, and the other contains details about transactions that have happened for each month. I am trying to get the total spend for last month and the month before. (I know I am not using anything from the CUS_ACC table at the mo, but I will need to)

SELECT PROD.CUST_ACC_NUM, Sum(SPEND) AS SUMSPEND, STMT_MONTH, STMT_YEAR
FROM PROD INNER JOIN CUST_ACC ON PROD.CUST_ACC_NUM=CUST_ACC.CUST_ACC_NUM

WHERE PROD.CUST_ACC_NUM=630822
And
(
(STMT_MONTH=Month(DateAdd("m",-1,Date())) And STMT_YEAR=Year(DateAdd("m",-1,Date())))
Or
(STMT_MONTH=Month(DateAdd("m",-2,Date())) And STMT_YEAR=Year(DateAdd("m",-2,Date())))
)
GROUP BY PROD.CUST_ACC_NUM, STMT_MONTH, STMT_YEAR;

now, as the query is at the moment, it takes tens of minutes to run for a single account (I will have to do it for about 3,000 accounts). However, if remove the line that starts with 'OR' and the line below it (i.e. only get last months sales) it runs in seconds.

Any ideas why?
Thanks
Kevinforget it, its working at full speed. hmmmmmm, very very strange

Can ODBC be used in Report Builder or Report Model?

Dear All,

Our company use DB2.

When I download Report Builder from Report site or when I create Report Model project in VS, I can only use SQL Server Data Source.

Can Report Builder or Report Model use ODBC?

Thank U.

I am pretty certain that report models can only be built from SQL server or Analysis Services data source. Support for other data sources has been rumored to be on the way, but who knows. This was a major disappointment for our company.|||

You can use ODBC as a data source, but in a semi-roundabout way.

1. In SSAS, create a UDM (cube with multiple data sources) which points to your ODBC data source

2. Build an SSRS Model off the UDM/Cube (You must do so thru Report Manager, VS does not support building models from SSAS...only SQL)

3. Use the Model...

|||Not directly in this release. You can do it indirectly using SQL Server linked tables or by creating an Analysis Services cube first, then creating a report model on top of that.|||

Where can I find the steps necessary on how to use linked server tables when creating a report model?

Thank you.

Patti Biggs

|||

First, go to BOL and search on 'sp_addlinkedserver'. Then here are a couple of links to get you started.

http://www.sql-server-performance.com/linked_server.asp

http://www.databasejournal.com/features/mssql/article.php/3085211

R

Can ODBC be used in Report Builder or Report Model?

Dear All,

Our company use DB2.

When I download Report Builder from Report site or when I create Report Model project in VS, I can only use SQL Server Data Source.

Can Report Builder or Report Model use ODBC?

Thank U.

I am pretty certain that report models can only be built from SQL server or Analysis Services data source. Support for other data sources has been rumored to be on the way, but who knows. This was a major disappointment for our company.|||

You can use ODBC as a data source, but in a semi-roundabout way.

1. In SSAS, create a UDM (cube with multiple data sources) which points to your ODBC data source

2. Build an SSRS Model off the UDM/Cube (You must do so thru Report Manager, VS does not support building models from SSAS...only SQL)

3. Use the Model...

|||Not directly in this release. You can do it indirectly using SQL Server linked tables or by creating an Analysis Services cube first, then creating a report model on top of that.|||

Where can I find the steps necessary on how to use linked server tables when creating a report model?

Thank you.

Patti Biggs

|||

First, go to BOL and search on 'sp_addlinkedserver'. Then here are a couple of links to get you started.

http://www.sql-server-performance.com/linked_server.asp

http://www.databasejournal.com/features/mssql/article.php/3085211

R