October 26, 2009 at 10:27 am
I have a view with three select statements and unionall , iam trying to pull records from a table which has 40 lkh records it is taking 2 min, i tried with procedure but i couldnt find much difference iam using union all as i need duplicate records also , can any one give me idea/sugestion to retrive records from large tables like this.
October 26, 2009 at 11:02 am
You will need the correct indexes to speed up the query.
Can you post the table and index structure as well as your query and execution plan?
October 26, 2009 at 10:31 pm
Hi Steve
Table Name: sale_Transactions
Followinga are the columns
Txn_IDnumeric(10, 0)
Txn_Typevarchar(10)
Txn_Modechar(1)
Prvd_IDvarchar(50)
Vendor_IDvarchar(50)
Payer_IDvarchar(50)
CSUser_IDvarchar(50)
CS_TxnIDvarchar(50)
Txn_Datedatetime
CS_Txn_Modevarchar(1)
Indexes are as follows
Clustered index is on 'Txn_ID' whic is having primary key
composite indexes is on 'Prvd_ID,Txn_Date' and 'Vendor_ID,Txn_Date'and 'Payer_ID,Txn_Date and 'CSUser_ID,Txn_Date
Here is my view
SELECT vw.UserType, vw.Txn_Date, vw.Txn_Mode, vw.Txn_Type, vw.Users
FROM (SELECT 'abc' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Prvd_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Prvd_ID
UNION ALL
SELECT 'def' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Vendor_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Vendor_ID
UNION ALL
SELECT 'ghi' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Payer_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Payer_ID
UNION ALL
SELECT 'xyz' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.CSUser_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.CSUser_ID) AS vw INNER JOIN
dbo.Txn_Types_Master AS MTT ON vw.Txn_Type = MTT.Txn_Type AND vw.Txn_Mode = MTT.Txn_Mode
October 26, 2009 at 10:49 pm
Hi Steve
Table Name: sale_Transactions
Followinga are the columns
Txn_IDnumeric(10, 0)
Txn_Typevarchar(10)
Txn_Modechar(1)
Prvd_IDvarchar(50)
Vendor_IDvarchar(50)
Payer_IDvarchar(50)
CSUser_IDvarchar(50)
CS_TxnIDvarchar(50)
Txn_Datedatetime
CS_Txn_Modevarchar(1)
Indexes are as follows
Clustered index is on 'Txn_ID' whic is having primary key
composite indexes is on 'Prvd_ID,Txn_Date' and 'Vendor_ID,Txn_Date'and 'Payer_ID,Txn_Date and 'CSUser_ID,Txn_Date
Here is my view
SELECT vw.UserType, vw.Txn_Date, vw.Txn_Mode, vw.Txn_Type, vw.Users
FROM (SELECT 'abc' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Prvd_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Prvd_ID
UNION ALL
SELECT 'def' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Vendor_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Vendor_ID
UNION ALL
SELECT 'ghi' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Payer_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.Payer_ID
UNION ALL
SELECT 'xyz' AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.CSUser_ID AS Users
FROM dbo.sale_Transactions AS ET INNER JOIN
dbo.users_master AS MU ON MU.USER_NAME = ET.CSUser_ID) AS vw INNER JOIN
dbo.Txn_Types_Master AS MTT ON vw.Txn_Type = MTT.Txn_Type AND vw.Txn_Mode = MTT.Txn_Mode
October 27, 2009 at 3:37 am
Can you also post the execution plan and table/index structure for the User_master table?
From an inital look it seems you could benefit from having an index on tx_type and txn_mode.
October 27, 2009 at 3:52 am
User_master table has nearly 30 columns, primary key is on user_name and one composite index is there which is created taking almost all columns and password filed is having non clustered index this is all about indexes related to the table.
October 27, 2009 at 4:36 am
Can you post the execution plan?
October 27, 2009 at 9:29 am
sorry i couldn't understand what it mean execution plan can u explain me once
October 27, 2009 at 10:11 am
When you run the query in SSMS click on the small icon that says 'Include Actual Execution Plan' this shows the plan that SQL is taking to run the query and should be one of the first things you look at when it comes to working with performance issues
have a look at this article on how to post the execution plan;
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 27, 2009 at 12:06 pm
I would imagine that much of your problem lies with the UNION ALL. You are forcing the query to access your large table multiple times, 4 in this case, to satisfy the query.
Have you tried using the UNPIVOT statement?
Here's an example that should produce the same (or similar) results as the query you posted:
DECLARE @sale_Transactions TABLE (
Txn_ID numeric(10, 0),
Txn_Type varchar(10),
Txn_Mode char(1),
Prvd_ID varchar(50),
Vendor_ID varchar(50),
Payer_ID varchar(50),
CSUser_ID varchar(50),
CS_TxnID varchar(50),
Txn_Date datetime,
CS_Txn_Mode varchar(1)
)
INSERT INTO @sale_Transactions
SELECT 1, 'Type1', 'M', 'Prvd 1', 'Vendor 1', 'Payer 1', 'CSUser 1', 'CS_TxnID', GETDATE(), 'M' UNION ALL
SELECT 2, 'Type1', 'M', 'Prvd 2', 'Vendor 2', 'Payer 2', 'CSUser 2', 'CS_TxnID', GETDATE(), 'M' UNION ALL
SELECT 3, 'Type1', 'M', 'Prvd 3', 'Vendor 3', 'Payer 3', 'CSUser 3', 'CS_TxnID', GETDATE(), 'M'
DECLARE @users_master TABLE (
User_name varchar(50)
)
INSERT INTO @users_master
SELECT 'Prvd 1' UNION ALL
SELECT 'Prvd 2' UNION ALL
SELECT 'Prvd 3' UNION ALL
SELECT 'Vendor 1' UNION ALL
SELECT 'Vendor 2' UNION ALL
SELECT 'Vendor 3' UNION ALL
SELECT 'CSUser 1' UNION ALL
SELECT 'CSUser 2' UNION ALL
SELECT 'CSUser 3'
DECLARE @Txn_Types_Master TABLE (
Txn_Type varchar(10),
Txn_Mode varchar(1)
)
INSERT INTO @Txn_Types_Master
SELECT 'Type1', 'M'
SELECT vw.UserType,
vw.Txn_Date,
vw.Txn_Mode,
vw.Txn_Type,
vw.Users
FROM (
SELECT CASE UserType
WHEN 'Prvd_ID' THEN 'abc'
WHEN 'Vendor_ID' THEN 'def'
WHEN 'Payer_ID' THEN 'ghi'
WHEN 'CSUser_ID' THEN 'xyz'
ELSE UserType END as UserType,
Txn_Date,
Txn_Mode,
Txn_Type,
Users
FROM (
SELECT Txn_ID,
Txn_Date,
Txn_Mode,
Txn_Type,
Prvd_ID,
Vendor_ID,
Payer_ID,
CSUser_ID
FROM @sale_Transactions) p
UNPIVOT
(Users FOR UserType IN
(Prvd_ID, Vendor_ID, Payer_ID, CSUser_ID)
) as unpvt
) AS vw
INNER JOIN @Txn_Types_Master AS MTT ON vw.Txn_Type = MTT.Txn_Type AND vw.Txn_Mode = MTT.Txn_Mode
October 27, 2009 at 12:48 pm
By the way - retrieving 4Million rows is bound to take some amount of time, espeically if you're pulling that "over the wire", to something like a report.
You may care to find out how fast your actual query is vs how long it take the results to move over the wre once it's been picked up (i.e. if you select a count of what you usually get back, so you get a decent idea how long it actually takes to find the rows you need. The rest of the time is how long 4Million rows take to transit from the server to the display mechanism.)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 28, 2009 at 4:01 am
Thank you john for you r favour but this is taking much time than my stuff as i am getting duplicate records...
October 28, 2009 at 8:14 am
I would think that doing 4 table scans and 4 joins on a table with 40M rows WILL take some time. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 28, 2009 at 8:42 am
yes but i don't have any other go any ideas/suggestions are welcome.
October 28, 2009 at 9:51 am
If all your links to user_name are unique, this or something similar could work:
SELECT CASE WHEN MU.USER_NAME = ET.Prvd_ID THEN 'abc'
WHEN MU.USER_NAME = ET.Vendor_ID THEN 'def'
WHEN MU.USER_NAME = ET.Payer_ID THEN 'ghi'
WHEN MU.USER_NAME = ET.CSUser_ID THEN 'xyz'
END AS UserType, ET.Txn_Date, ET.Txn_Mode, ET.Txn_Type, ET.Prvd_ID AS Users
FROM dbo.sale_Transactions AS ET
INNER JOIN users_master AS MU ON
(MU.USER_NAME = ET.Prvd_ID
OR MU.USER_NAME = ET.Vendor_ID
OR MU.USER_NAME = ET.Payer_ID
OR MU.USER_NAME = ET.CSUser_ID)
INNER JOIN dbo.Txn_Types_Master AS MTT ON ET.Txn_Type = MTT.Txn_Type AND ET.Txn_Mode = MTT.Txn_Mode
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply