AlexKuznetsov

3,607 reputation

1820

bio	website	sqlblog.com/blogs/…
	location	Chicago, IL
	age
visits	member for	1 year, 5 months
	seen	23 hours ago
stats	profile views	232

Developer

	3,607 reputation	bio	website	sqlblog.com/blogs/…	visits	member for	1 year, 5 months
1820 badges		location	Chicago, IL		seen	23 hours ago

summary answers questions tags badges favorites bounties reputation activity

501 Actions

suggestions reviews revisions comments badges posts accepts all

2d	awarded	Self-Learner
May 13	awarded	Caucus
May 13	comment	Gaps and islands: client solution vs T-SQL query @MarkStorey-Smith I apologize for the delay: I needed to complete a project.
May 2	awarded	Popular Question
Apr 18	comment	Only allow one checked row in a Column in SQL Server Remus, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now.
Apr 18	comment	Only allow one checked row in a Column in SQL Server +1 I would do the same thing. In fact, this is a common requirement. For example, I have a large to-do list, but there is at most one thing I am working on right now.
Apr 17	comment	Unique Constraint with multiple null columns I cannot figure out what your problem is. Can you post your CREATE TABLE statemnts?
Apr 12	comment	Would using timestamps solve consistency issues with snapshot isolation? I would not ever suggest turning on READ_COMMITTED_SNAPSHOT at the database level, at least not without very serious code review, because it can introduce lots of subtle bugs.
Apr 9	comment	Hash in SQL query What is your RDBMS?
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand if there is no limit on length of intervals, then I need to scan a huge range even if I only need gaps during one hour. Still, I think although I simplified the problem as much as possible, yet it still resembles real life ones.
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @MarkStorey-Smith sure. Can you provide more details - what column types/widths to add?
Apr 5	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand Can you provide an example of "abstracted the data from the tables into properly optimized temp tables". Also can you explain what do you mean by "Islands aren't sets. They're the absence of sets".
Apr 5	revised	Gaps and islands: client solution vs T-SQL query added 78 characters in body
Apr 4	comment	Gaps and islands: client solution vs T-SQL query @jcolebrand yes I think so. Relational theory assumes unordered data sets, but temporal data inherently ordered, so RDBMS is not quite cut out to work with temporal data. What do you think?
Apr 4	comment	Gaps and islands: client solution vs T-SQL query @PeterLarsson can you suggest a better way to benchmark? Writing to a file mimics quite slow consumption of data by the client.
Apr 4	comment	Gaps and islands: client solution vs T-SQL query This is about 30% faster than your other solution. 1 gap: (00:00:12.1355011 00:00:11.6406581), 2M-1 gaps (00:00:12.4526817 00:00:11.7442217). Still this is about 25% slower than the client side solution in its worst case, exactly as predicted by Adam Machanic on twitter.
Apr 4	revised	Gaps and islands: client solution vs T-SQL query added 146 characters in body
Apr 4	comment	Gaps and islands: client solution vs T-SQL query Peter, on a data set with one gap this is more than 10 times slower: (00:00:18.1016745 -- 00:00:17.8190959) On the data with 2M-1 gaps, it is 2 times slower: (00:00:17.2409640 00:00:17.6068879)
Apr 4	revised	Gaps and islands: client solution vs T-SQL query added 132 characters in body
Apr 4	comment	Gaps and islands: client solution vs T-SQL query Most applications want to use IEnumerable<SomeClassOrStruct> - in this case we just yield return instead adding a line to a list. To keep this example short, I have removed lots of things not essential to measuring raw performance.