SQL query help & optimization (cursor or subquery)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query help & optimization (cursor or subquery)

Author

Topic

dew3it
Starting Member

14 Posts

Posted - 2008-12-29 : 01:54:45

Hi

I need to write a query in SQL Server 2005 for below mentioned resultset and I am looking for some optimal way of doing it beacuse of large data in tables.

Begin Tran

create table #temp(MGID varchar(15), MGType Varchar(6))

create table #temp1(MGID varchar(15), CoperID integer)

insert into #temp (MGID,MGType) values ('ABC','Risk')
insert into #temp (MGID,MGType) values ('ABS1','N-Risk')

insert into #temp1 (MGID,CoperID) values ('ABS1',11)
insert into #temp1 (MGID,CoperID) values ('ABS1',22)
insert into #temp1 (MGID,CoperID) values ('ABS1',33)
insert into #temp1 (MGID,CoperID) values ('ABS1',44)

-- #temp and #temp1 (relation-MGID)
--Result Required

-- MGID MGType CoperIDs

-- ABC Risk 11,22

-- ABS1 N-Risk 33,44

drop table #temp

drop table #temp1

rollback tran

I am not query expert,can somebody suggest on it?

---------------------------------------------------------------------Dev

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 04:10:53

didnt understand your sample data. how did you correlate ABC Risk to ABS1 values 11,22?can you explain that?

jbp_j
Starting Member

24 Posts

Posted - 2008-12-29 : 04:18:33

HI,

I think this is u r required input and output.

Try this one.

BEGIN TRY

BEGIN TRAN

create table #temp(MGID varchar(15), MGType Varchar(6))

create table #temp1(MGID varchar(15), CoperID integer)

insert into #temp (MGID,MGType) values ('ABC1','Risk')
insert into #temp (MGID,MGType) values ('ABS1','N-Risk')

insert into #temp1 (MGID,CoperID) values ('ABC1',11)
insert into #temp1 (MGID,CoperID) values ('ABC1',22)
insert into #temp1 (MGID,CoperID) values ('ABS1',33)
insert into #temp1 (MGID,CoperID) values ('ABS1',44)

SELECT * FROM #temp

SELECT * FROM #temp1

SELECT MGID,MGType,STUFF((SELECT ','+ CAST(Coperid AS VARCHAR(20))
FROM #temp1 AS t1 where t1.MGID = t.MGID FOR XML PATH('')),1,1,'')
FROM #temp AS t

-- #temp and #temp1 (relation-MGID)
--Result Required

-- MGID MGType CoperIDs

-- ABC Risk 11,22

-- ABS1 N-Risk 33,44

drop table #temp

drop table #temp1

COMMIT TRAN

END TRY

BEGIN CATCH

ROLLBACK TRAN

SELECT @@ERROR

END CATCH

dew3it
Starting Member

14 Posts

Posted - 2008-12-30 : 01:11:27

Thanks jbp,
I wrote the same query using cursor and I got one more solution for this issue using following line of code which one would be the best with regard to performance in case large data?

Set Nocount On
Print ‘Start’
Print ‘Creating Sample Items table’
Go
CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO
Print ‘Inserting data in sample table’
Go

Insert into dbo.tblItems (ItemName) Values (‘Item1')
Insert into dbo.tblItems (ItemName) Values (‘Item2')
Insert into dbo.tblItems (ItemName) Values (‘Item3')
Insert into dbo.tblItems (ItemName) Values (‘Item4')
Go
Print ‘Following is the comma separated list of Items’
Go
Declare @m_ItemsList nvarchar(2000)
Select @m_ItemsList=”
Select @m_ItemsList=@m_ItemsList+ItemName+‘,’ from tblItems
Select Left(@m_ItemsList,Len(@m_ItemsList)-1)
Go
Print ‘Dropping sample table’
Drop table dbo.tblItems
Go
Print ‘Finished’

dev

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 01:13:55

Compare the execution plans to determine what is best for performance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dew3it
Starting Member

14 Posts

Posted - 2008-12-30 : 10:33:03

Thanks Tara,

In my query CoperID is of type "uCoperID:decimal(38, 0)"
what would you suggest the size of Varchar(...) ,currenlty I have used Varchar(max)
but I am not sure about the dynamic size handling of it ,could it lead to unneccessary space occupancy?
i.e. minimum 8060 char for each row

SELECT MGID,MGType,STUFF((SELECT ','+ CAST(Coperid AS VARCHAR(20))
FROM #temp1 AS t1 where t1.MGID = t.MGID FOR XML PATH('')),1,1,'')
FROM #temp AS t

---------------------------------------------------------------------

SELECT MGID,MGType,STUFF((SELECT ','+ CAST(Coperid AS VARCHAR(max))
FROM #temp1 AS t1 where t1.MGID = t.MGID FOR XML PATH('')),1,1,'')
FROM #temp AS t

or should I go with VarChar(38)?

Please advice.

----
Dev

dew3it
Starting Member

14 Posts

Posted - 2008-12-30 : 10:42:25

visakh16
my mistake,It should be

insert into #temp (MGID,MGType) values ('ABC','Risk')
insert into #temp (MGID,MGType) values ('ABS1','N-Risk')

insert into #temp1 (MGID,CoperID) values ('ABC',11)
insert into #temp1 (MGID,CoperID) values ('ABC',22)
insert into #temp1 (MGID,CoperID) values ('ABS1',33)
insert into #temp1 (MGID,CoperID) values ('ABS1',44)

-- #temp and #temp1 (relation-MGID)
--Result Required

quote:
Originally posted by visakh16

didnt understand your sample data. how did you correlate ABC Risk to ABS1 values 11,22?can you explain that?

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:59:55

quote:
Originally posted by dew3it

Thanks Tara,

In my query CoperID is of type "uCoperID:decimal(38, 0)"
what would you suggest the size of Varchar(...) ,currenlty I have used Varchar(max)
but I am not sure about the dynamic size handling of it ,could it lead to unneccessary space occupancy?
i.e. minimum 8060 char for each row

SELECT MGID,MGType,STUFF((SELECT ','+ CAST(Coperid AS VARCHAR(20))
FROM #temp1 AS t1 where t1.MGID = t.MGID FOR XML PATH('')),1,1,'')
FROM #temp AS t

---------------------------------------------------------------------

SELECT MGID,MGType,STUFF((SELECT ','+ CAST(Coperid AS VARCHAR(max))
FROM #temp1 AS t1 where t1.MGID = t.MGID FOR XML PATH('')),1,1,'')
FROM #temp AS t

or should I go with VarChar(38)?

Please advice.

----
Dev

if its decimal (38,0) then max digits it can contain is 38 so varchar(38) will be sufficient

dew3it
Starting Member

14 Posts

Posted - 2008-12-31 : 07:05:17

Thanks Visakh,
for safer side I am using Varchar(50).

dev

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources