Stack Exchange
log in

Martin Smith

 Room for Zikato and Martin Smith

Martin Smith
Oct 15, 2024 13:58
yeah - it's a very common name!
Martin Smith
Oct 15, 2024 13:51
I just found you from the link on straightforwardsql.com so have sent a connection request!#
Martin Smith
Oct 15, 2024 13:48
Works on my machine (so far) anyway! :-)
Martin Smith
Oct 15, 2024 13:47
Of course! Mine is fairly untested. I think the approach should work OK with the special casing for time parts that would be above 23:59:59.999999
Martin Smith
Oct 15, 2024 13:43
Oh! Sorry I didn't see your post!
Martin Smith
Oct 15, 2024 13:38
Hiya, no probs. Hopefully they just fix the issue so it becomes obsolete!
 

 The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....
9
7
Martin Smith
Sep 26, 2024 09:23
Hi all. Could do with some spam flags here so it gets auto deleted dba.stackexchange.com/questions/220738/…
Martin Smith
May 14, 2020 10:48
@Aaron Bertrand♦ Thanks for fixing all the links to SQLBlog and broken Microsoft blogs!
Martin Smith
Apr 19, 2020 16:46
needs some spam flags. dba.stackexchange.com/q/265388/3690
Martin Smith
Jan 16, 2020 19:54
@MichaelGreen LOBs use tempdb. Other datatypes it is allocated up front in the memory for the execution context. Query to see the effect of declaring more variables on size of memory is here. social.msdn.microsoft.com/Forums/sqlserver/en-US/…
Martin Smith
Jan 13, 2020 21:24
@MaxVernon yes I was aware Jack had resigned here.
Martin Smith
Jan 13, 2020 21:18
@MaxVernon thanks, I'll take a look now
Martin Smith
Jan 13, 2020 21:13
Thanks, and hello back! I haven't been spending that much time on SE lately but I see it seems to have lurched into some other drama today
Martin Smith
Jan 13, 2020 21:08
This Q might be better off over here. stackoverflow.com/q/59720718/73226
 

 Discussion between ttugates and Marti…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
Jan 17, 2023 19:45
It doesn't need to be done to the column it can just be specified in the query
Martin Smith
Jan 17, 2023 19:44
If the data can be stored in Upper case in the first place then you can remove the UPPER there. And similarly if the parameter values being searched for can be upper cased first then you can remove those Upper too. The binary collate clause is quicker because it just looks at a match at the binary level so won't do any case insensitive comparisons etc
Martin Smith
Jan 17, 2023 19:40
You will get results if you add the UPPER back in
Martin Smith
Jan 17, 2023 19:20
using the binary collation sped things up from 500ms to 100ms on my dummy data
Martin Smith
Jan 17, 2023 19:19
So dbfiddle.uk/XlusIaOD
Martin Smith
Jan 17, 2023 19:15
Also your DB Fiddle has lost the COLLATE Latin1_General_100_BIN2 - this made a big improvement speedup for me
Martin Smith
Jan 17, 2023 19:14
etc.
Martin Smith
Jan 17, 2023 19:14
This is why in the answer it had " ' ' + [1]"
Martin Smith
Jan 17, 2023 19:13
dbfiddle.uk/LMa8MIMO
Martin Smith
Jan 17, 2023 19:05
yeah feel free to paste some code and I'll take a look
Martin Smith
Jan 17, 2023 19:03
Have you got it working now?
Martin Smith
Jan 17, 2023 19:00
So for all the CHARINDEX calls both the expression being searched for and the string being searched have been left padded out with a single space and so this should work out the same as the LIKE b.[value] + '%' logic after splitting on space.
Martin Smith
Jan 17, 2023 19:00
@ttugates - the query in my answer works fine., from your comment above it looks like you dropped the CONCAT(' ', when rewriting it., This was the purpose of that
Martin Smith
Jan 17, 2023 19:00
@ttugates - I just noticed what you said about SELECT 'car' AS [0] - in that case you don't need QSplit at all. You can just start the query at SELECT m.*, Rank and instead of CHARINDEX([1],SpacePadded) do CHARINDEX(' ' + @Param1,SpacePadded) etc - and pass the parameter values as upper cased individual values
Martin Smith
Jan 17, 2023 19:00
@ttugates - in that case you can get rid of UPPER(Data) and just use Data as the UPPER won't do anything there and will take some CPU time
 

 Discussion on question by Dave: Why a…

Imported from a comment discussion on money.stackexchange.com/...
Martin Smith
Jan 19, 2021 14:48
@I'mwithMonica - or not even a piece of paper sometimes. Apparently a live cow has also sufficed theguardian.com/notesandqueries/query/0,5753,-20434,00.html
 

 Discussion on question by SSimon: Did…

Imported from a comment discussion on skeptics.stackexchange.c...
Martin Smith
Jun 5, 2020 10:06
On the democracy angle see, for example, Cambridge Analytica
 

 Discussion on question by okarpov: T-…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
May 21, 2020 17:59
Forget about testing this in SSMS. It won't be representative of what is happening in the application given that 97% of the time spent is ASYNC_NETWORK_IO. There seems to be no tuning needed on the SQL Server side as the seek takes 3ms
Martin Smith
May 21, 2020 17:59
Also are you seeing this 100ms duration from your application or just in SSMS?
Martin Smith
May 21, 2020 17:59
So the above measures the SELECT plus some additional overhead but takes the client out of the equation
Martin Smith
May 21, 2020 17:59
Did you understand that the idea was instead of SELECTing them to return to the client? I.E. declare @DeskModelID nvarchar(128)=N'abcdefgh' select ModelID INTO #temp from dbo.tblTask where DeskModelID=@DeskModelID
Martin Smith
May 21, 2020 17:59
That is the time spent sending the results to the client. If the client loops through the results and spends time doing processing of each one before reading the next this can get high So probably you are just measuring the performance of the gridview in SSMS
Martin Smith
May 21, 2020 17:59
I've no idea what you mean by "no session id in the list - so no any wait". Have a look at the wait stats in the actual execution plan XML. If it runs for 100ms it will definitely encounter waits
Martin Smith
May 21, 2020 17:59
you need to look at the wait stats for the execution. Is it encountering blocking? Is it waiting on network IO? Is it waiting on disc? the screen shot you gave shows the seek taking 3 ms so I imagine asynch network io will be the wait type
 

 Discussion on answer by Jon of All Tr…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
Jan 17, 2020 09:32
@Pac0 this is as per the SQL standard. support.microsoft.com/en-us/help/316626/…
 

 Discussion on answer by Avery: Were t…

Imported from a comment discussion on skeptics.stackexchange.c...
Martin Smith
Dec 24, 2019 12:46
@candied_orange - I think the issue is due to Nikki Henderson flying out from the UK to US to skipper the boat - not the carbon footprint of the two modes of transport. So maybe the question title should be edited to be less "bait and switch"
 

 Discussion on question by jitbit: SQL…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
Nov 20, 2019 09:13
@SQLRaptor The seek part doesn't really have any direction. It is down through the levels of the B tree to find the point where the scan starts. Then the scan part goes backwards or forwards to scan from that point It can certainly seek to the end of the range and go backwards. Look at the plan for this CREATE TABLE [dbo].[Comments] ( [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY, [UserID] int NULL index ix_userid, [Body] nvarchar(max) NOT NULL ) declare @UserID int; SELECT TOP 50 CommentID FROM Comments WHERE UserID=@UserID ORDER BY CommentID DESC i.stack.imgur.com/a2pdi.png
Martin Smith
Nov 20, 2019 09:13
@SQLRaptor no, you're wrong. See web.archive.org/web/20190308085816/sqlblog.com/blogs‌​… For non unique indexes it is silently added in to the key columns. So the key columns should already be UserId,CommentId which can be seeked into from the end of the range and read in backwards direction to provide the desired order with no sort
Martin Smith
Nov 20, 2019 09:13
Actually - there is one strange thing. The UserID index will already have CommentID in it as that is the CI key - and that will be appended to the key columns to make it unique as clearly UserId isn't unique in its own right. So it should just be able to seek into that and get the results without needing a sort at all. Can you script out the full table definition including all indexes?
 

 Discussion on question by Vladimir Ba…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
Jun 21, 2019 12:35
I don't know the gory details of why the optimiser behaves in this way. IIRC there are other missing optimisations too where window functions with the same window specification can have an extra sort operation if separated in the select list by one with a different specification.
Martin Smith
Jun 21, 2019 12:35
@ypercubeᵀᴹ an order by in the derived table (like option 2 here dba.stackexchange.com/questions/135764/…)
Martin Smith
Jun 21, 2019 12:35
@ypercubeᵀᴹ Agreed it's not exactly the same. The solution of adding an order by may well work though. And if it does then could use a derived table with top big number ... order by and then select from that with final order by c.
Martin Smith
Jun 21, 2019 12:35
There's a somewhat similar issue where an index on a,b,c wont be used for partition by a,b order by c desc (By scanning backwards) except if the query has an order by a desc, b desc, c desc. So in that case adding an order by can remove a sort.
 

 Discussion on question by Steve: spee…

Imported from a comment discussion on dba.stackexchange.com/qu...
Martin Smith
Jun 8, 2019 15:59
you need to order by enough columns to make the order fully deterministic. So probably adding locationid in for this different example
Martin Smith
Jun 8, 2019 15:59
For the example table you have given here you would need to change the order by to ORDER BY Item.Name, Item.ID so it is deterministic in the event of ties and then pass both in to get the next 10 rows after that record
Martin Smith
Jun 8, 2019 15:59
can you use this approach instead of calculating it all from scratch each time? dba.stackexchange.com/a/224754/3690