October 5, 2015 at 8:42 am
ps_vbdev (10/5/2015)
Its another team that are in-charge of the source db so Im not sure why these datatypes have been selected, I know c# guids are used all over the place for ids (UserID & StockTransactionType) but they never exceed 36 nvarchar.
GUIDs should be stored in the UNIQUEIDENTIFIER data type, not varchar (and they're not unicode, so they don't need nvarchar, a GUID contains 0-9 and A-F). Please have a chat with the other team about fixing the data types, a poor design almost always leads to poor performance.
WHERE
DATEPART(isowk, pish.[InductedDate]) >= DATEPART(isowk, GETDATE())-1 AND pish.[InductedDate] <= DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))
That's a table scan, no matter what indexes you have. If you partition, it'll still be a full table scan, reading all partitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2015 at 8:53 am
Ill have a chat with them and clarify this. Once i wrap my sql in sp's i can add the calculation.
Thanks ever so much guys 🙂
October 5, 2015 at 8:55 am
The upcoming 'Stretch Database' feature in SQL 2016 may be exactly what you are looking for. I know this doesn't help you at the moment but it might be something to keep an eye on. Personally I think it's a great idea in theory and I'd love to give a go...one day if I can ever get them to upgrade to an SQL version that isn't already 2 versions back. :crazy:
https://msdn.microsoft.com/en-CA/library/dn935011.aspx
October 5, 2015 at 9:00 am
yb751 (10/5/2015)
The upcoming 'Stretch Database' feature in SQL 2016 may be exactly what you are looking for.
With poor queries, that'll just make matters worse. If the queries can't eliminate the data in Azure (which with the function on the date column they won't be able to), SQL still has to read all the table, only now it'd have to fetch part of it down from Azure first, instead of off disk.
Stretch is mostly for alleviating storage problems, not a fix for badly performing queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 5, 2015 at 9:57 am
GilaMonster (10/5/2015)
yb751 (10/5/2015)
The upcoming 'Stretch Database' feature in SQL 2016 may be exactly what you are looking for.With poor queries, that'll just make matters worse. If the queries can't eliminate the data in Azure (which with the function on the date column they won't be able to), SQL still has to read all the table, only now it'd have to fetch part of it down from Azure first, instead of off disk.
Stretch is mostly for alleviating storage problems, not a fix for badly performing queries.
Good point...however just solely on his original post it does appear to be the strategy he aiming for, I wasn't accounting for any shortcomings in the original design. Maybe I'll just walk away in virtual shame now... :pinch:
October 6, 2015 at 3:47 am
GilaMonster (10/5/2015)
Please post the most common queries.Also, why Datetimeoffset? It's not a common data type choice, especially since it's not daylight savings aware, which reduces the usefulness of storing a time zone
Why is UserID an NVarchar(Max)? I can't recall last time I saw a userID over 4000 characters long.
Why is StockTransactionType nvarchar(max)? You really have types that are several thousand characters long?
Why are there ID columns which are nvarchar?
Fixing what looks like some design problems will get you much better performance gains than partitioning. The MAX data types are probably killing your query performance, they and the nvarchar IDs may well be why the table is so hard to query.
Interesting stuff, iv just move to this job and i finally get the opportunity to spend some time developing my knowledge rather than just producing. No idea why offset was used we don't even operate in different timezones. Im interested to know, regarding the use of nvarchar and nvarchar(max) what is exactly happening in the background that would be slowing the process down?
October 6, 2015 at 3:56 am
Implicit conversions most likely. Also they're much larger data types than you need. A GUID stored in a nvarchar column will take 72 bytes, vs 16 if stored in a uniqueidentifier, multiply that by 6 columns and 50 million rows, and your table is 16GB larger than it would be with the correct data types.
That makes your rows larger and hence means more work for SQL in processing the query. If any of the MAX data types are storing data off-page (which is what they're basically there for, to store data that won't fit on a 8k page), then it'll add significantly more work to the query processing as the extra data has to be fetched separately.
Joining on string columns is also slower than int/uniqueidentifier, grouping by them is probably also slower (haven't tested).
Those, combined with the functions on columns in the query basically ensure that your queries have to read the entire table every time, and have to read a table that's much larger than necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 4:22 am
Thanks Gilamonster.
this is the first place ive been where ids aren't in some form of integer, i think this is a combination of legacy & lazyness. Your explanation makes total sense but where would the implicit conversation take place?
October 6, 2015 at 6:46 am
GilaMonster (10/5/2015)
yb751 (10/5/2015)
The upcoming 'Stretch Database' feature in SQL 2016 may be exactly what you are looking for.With poor queries, that'll just make matters worse. If the queries can't eliminate the data in Azure (which with the function on the date column they won't be able to), SQL still has to read all the table, only now it'd have to fetch part of it down from Azure first, instead of off disk.
Stretch is mostly for alleviating storage problems, not a fix for badly performing queries.
Yep, and for the benefit of storage in the cloud, you're going to lose the benefit of speed in the query if that happens. Someone can correct me here if I'm wrong, but it's been noted that query performance will decrease if that happens.
Outside of that, if you're not using the data that may be stretched, what's the point?
October 6, 2015 at 6:54 am
ps_vbdev (10/6/2015)
Your explanation makes total sense but where would the implicit conversation take place?
In your queries, if you're ever doing anything like
WHERE UserID = @ParameterOfTheUniqueidentifierType, or anything similar
And if the app is sending parameterised queries, that could be happening very easily, since the IDs are GUIDs in .net.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2015 at 6:57 am
ah right got you. thanks for all the help 🙂
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply