March 22, 2017 at 2:30 pm
following qry is taking more than 3 hrs to run , it has billion records in each table, is there an alternate way to achieve
SyntaxEditor Code Snippet CREATE TABLE RevenueSPR AS(SEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, 'Med' AS RevSourceFROM vwREVPA aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='PA'UNIONSEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, RevSourceFROM vwREVMO aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='MO')
March 22, 2017 at 2:48 pm
Tara-1044200 - Wednesday, March 22, 2017 2:30 PMfollowing qry is taking more than 3 hrs to run , it has billion records in each table, is there an alternate way to achieveSyntaxEditor Code Snippet CREATE TABLE RevenueSPR AS(SEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, 'Med' AS RevSourceFROM vwREVPA aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='PA'UNIONSEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, RevSourceFROM vwREVMO aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='MO')
This isn't a valid T-SQL statement, so I'll assume that you're not using SQL Server.
You have a view that might be part of the problem if it's too complex. You're using UNION and you might want to use UNION ALL unless you have duplicates.
Here's a shorter version that might not work in your case.
CREATE TABLE RevenueSPR AS(
SELECT EmployeeID,
RequestNum,
a.BudgetProID,
c.RevCode BudgetCode,
CASE WHEN Revsource = 'PA' THEN 'Med' ELSE RevSource END AS RevSource
FROM vwREVPA a
INNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProID
INNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProID
WHERE RevSource='PA'
OR RevSource='MO');
For better help, go to a forum for the RDBMS that you're using and post DDL for the tables, views and indexes involved, along with an execution plan.
March 22, 2017 at 2:51 pm
Tara-1044200 - Wednesday, March 22, 2017 2:30 PMfollowing qry is taking more than 3 hrs to run , it has billion records in each table, is there an alternate way to achieveSyntaxEditor Code Snippet CREATE TABLE RevenueSPR AS(SEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, 'Med' AS RevSourceFROM vwREVPA aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='PA'UNIONSEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, RevSourceFROM vwREVMO aINNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProIDINNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProIDWHERE RevSource='MO')
Here's that one-liner looking less funny:
CREATE TABLE RevenueSPR AS(
SEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, 'Med' AS RevSource
FROM vwREVPA a
INNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProID
INNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProID
WHERE RevSource='PA'
UNION
SEL EmployeeID,RequestNum,a.BudgetProID,c.RevCode BudgetCode, RevSource
FROM vwREVMO a
INNER JOIN pre_division1 b ON a.BudgetProID=b.BudgetProID
INNER JOIN RegisterDB.DivisionCode c ON b.BudgetProID=c.BudgetProID
WHERE RevSource='MO')
Can you repost your query please? This isn't SQL Server 2008 syntax.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 22, 2017 at 3:21 pm
Not a solution to the problem but one of my biggest pet peeves with queries is aliases a, b, c...I am not alone in this either. It really make debugging and everything a LOT harder than it needs to be.
The other issue is that this site is dedicated to sql server and the query you posted is MySql syntax. You will find more help at a dedicated MySql forum.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 22, 2017 at 4:24 pm
That looks like SQL Server Parallel Data Warehouse, or Azure SQL Datawarehouse.
Is that the case? If so, which one?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2017 at 9:23 am
sorry for not mentioning that here but yes this is from PDW.
March 23, 2017 at 9:37 am
Ok, so firstly for those saying this isn't T-SQL, it is. It's just the variant of T-SQL used in PDW/Azure SQLDW (they have a CREATE TABLE AS statement, and up until very recently, no SELECT ... INTO)
Can you post/attach the EXPLAIN plan please, along with the table definitions including their distribution settings?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply