SQL Server Forums
Profile | Register | 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?
 New Topic  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
30117 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
30117 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
1222 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
1222 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
 New Topic  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.11 seconds. Powered By: Snitz Forums 2000