February 28, 2017 at 11:56 am
q
February 28, 2017 at 1:54 pm
It's nice to have the query, but as you've said absolutely nothing about the number of records in each table, or for that matter, anything about what this query is supposed to accomplish, I don't see that I could offer much other than to suggest one change that might improve things. You have a function in a WHERE clause, that doesn't need to be there. You can substitute:
WHERE MessageType IN ('CardAdmitted')
AND PrimaryObjectName <> ''
for:
WHERE MessageType IN ('CardAdmitted')
AND LEN(PrimaryObjectName) > 0
Until you can provide a LOT more detail as well as some consumable sample data and expected results, or perhaps a query execution plan, I wouldn't expect to be able to do much with this.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 28, 2017 at 2:53 pm
sgmunson - Tuesday, February 28, 2017 1:54 PMIt's nice to have the query, but as you've said absolutely nothing about the number of records in each table, or for that matter, anything about what this query is supposed to accomplish, I don't see that I could offer much other than to suggest one change that might improve things. You have a function in a WHERE clause, that doesn't need to be there. You can substitute:
WHERE MessageType IN ('CardAdmitted')
AND PrimaryObjectName <> ''for:
WHERE MessageType IN ('CardAdmitted')AND LEN(PrimaryObjectName) > 0
Until you can provide a LOT more detail as well as some consumable sample data and expected results, or perhaps a query execution plan, I wouldn't expect to be able to do much with this.
I don't think it will be a noticeable performance improvement, but a string value can never be less than ''. So PrimayObjectName > '' would also work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 1, 2017 at 6:22 am
drew.allen - Tuesday, February 28, 2017 2:53 PMsgmunson - Tuesday, February 28, 2017 1:54 PMIt's nice to have the query, but as you've said absolutely nothing about the number of records in each table, or for that matter, anything about what this query is supposed to accomplish, I don't see that I could offer much other than to suggest one change that might improve things. You have a function in a WHERE clause, that doesn't need to be there. You can substitute:
WHERE MessageType IN ('CardAdmitted')
AND PrimaryObjectName <> ''for:
WHERE MessageType IN ('CardAdmitted')AND LEN(PrimaryObjectName) > 0
Until you can provide a LOT more detail as well as some consumable sample data and expected results, or perhaps a query execution plan, I wouldn't expect to be able to do much with this.
I don't think it will be a noticeable performance improvement, but a string value can never be less than ''. So PrimayObjectName > '' would also work.
Drew
That's why I said "might"... It was the only thing in the query that had anything "obvious" about it, although clearly, in this case, there probably wasn't much to be gained. The original poster needs to provide a lot more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 1, 2017 at 8:45 am
I agree... without more information there isn't much anybody can tell you.
As a thought, which CTE is slow?
I'd test that first. Select from each CTE 1 at a time (or just run the SELECT statement from the CTE outside of the CTE) and see how long they each take to complete. But there are hundreds of things it could be... memory pressure, bad indexes, bad statistics, insufficient disk space, slow disk, tons of records/calculations, etc...
I also notice you are using different styles of coding in 1 query... that is bad practice. I highly recommend you include the schema in all your table/view calls and include the table name or alias before all columns. This won't affect performance, but increases readability.
Are you pulling data from 2 different databases as well?
What do your estimated execution plans look like? Do they seem reasonable (row counts look correct for example)? Do you have any blocking?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply