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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can This Be Made Set-Based?
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/10/2012 :  11:13:54  Show Profile  Reply with Quote
quote:
Originally posted by Vinnie881

see if this query makes more sense for you:

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881



Vinnie,
Thank you! This query actually makes perfect sense to me - it forms the backbone of the query I ended up coming up with.

It's fast - it takes about 0.1 seconds to run. Unfortunately, it returns duplicates. I greatly appreciate your assistance, but don't want you to spin your wheels when there are more important items for you to apply your brain-power to.

Unless someone knows some sneaky optimization technique, my query, as ugly as it is, works. While not blazingly fast, it isn't a complete slowpoke, either.

Result Set from Query:

Rowid	workingid	questid	topicid	questionsfortopic	keep
2	8	1002	AAT-AC-T3	2.00	
1	13	1002	AAT-TR-T1	1.00	
1	22	1014	AAT-TR-T2	1.00	
1	40	1014	PR	        2.00	
2	41	1015	PR      	2.00	
1	60	1082	SC      	2.00	
1	1	1082	AAT-AC-T1	2.00	
2	2	1148	AAT-AC-T1	2.00	
1	7	1148	AAT-AC-T3	2.00	
1	62	1148	RC      	1.00	
2	61	1187	SC      	2.00	
1	31	1187	AAT-TR-T3	1.00	


Question: Would there be any performance gain (or penalty) if I added a primary key to the QuestionID column on @TempTestQuestions? I don't expect that we will ever have more than 5,000-10,000 questions total. Would the overhead of the index cancel out the performance gains? What would happen if we did have a large data set, say hundreds of thousands or millions of rows? Would the key become cost effective then?

FischMan

Edited by - FischMan2 on 01/10/2012 11:48:51
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 01/10/2012 :  11:27:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Post some proper sample data and expected output!


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 01/10/2012 :  14:56:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Can you repost that using table variables and propert insert statements?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1231 Posts

Posted - 01/10/2012 :  15:43:55  Show Profile  Reply with Quote
If you are simply looking to eliminate duplicate questions, you can do it a lot of ways. Here's one.


Select * 
from
(
		select row_number() over (partition by topicid order by  (select 1)) as Rowid,*
		from
		(
		select row_number() over (partition by questionid order by  (select 1)) as questionrow,*
		from 
			@working
		) bb
		where bb.questionrow = 1
) aa
where  aa.rowid <= aa.questionsfortopic



Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Edited by - Vinnie881 on 01/10/2012 15:44:35
Go to Top of Page

FischMan2
Yak Posting Veteran

USA
59 Posts

Posted - 01/10/2012 :  17:36:46  Show Profile  Reply with Quote
quote:
Originally posted by Vinnie881

If you are simply looking to eliminate duplicate questions, you can do it a lot of ways. Here's one.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881



Vinnie,
I like this. It is clever, and it produces a good result set. It takes ~0.2 seconds to run, as well.


Thank you!

FischMan
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1231 Posts

Posted - 01/10/2012 :  20:19:18  Show Profile  Reply with Quote
you might get better performance with this:


Select * 
from
(
select row_number() over (partition by topicid order by  (select 1)) as Rowid,*
from 
@working a
inner join
(select min(ab.workingid) as workingid,ab.questionid
from @working ab
group by ab.questionid
) cc
on a.workingid = cc.workingidid
) aa
where  aa.rowid <= aa.questionsfortopic


or create a indexed second table for


select min(ab.workingid) as workingid,ab.questionid
from @working ab
group by ab.questionid


and join it the same way as illustrated in this post.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Edited by - Vinnie881 on 01/10/2012 20:26:31
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000