I am considering migrating all of my custom SQL alerts over to custom SWQL ostensibly to insulate myself from schema changes.
Am I making a mistake?
Dunno about the goal itself as the SWQL schema could change.
As for which is faster I'd suggest SQL is better (possibly faster). Mainly because in SQL you can use with(NOLOCK) on joins an what not and this reduces blocking.
SWQL runs directly against SWIS (from my understanding, not a programmer ) so I would assume that the 'nolock' usage is a moot point. Apples to oranges and all that.
But this is a really interesting question. SQL has some definite benefits over SWQL with reporting based on a lack of certain functions in the SWQL syntax. But for alerting, that's a different ballgame since it's more straightforward. Personally, I rather enjoy SWQL these days for the ease of access to data that sometimes requires 3 or 4 joins in a SQL statement.
My gut feeling would be that SWQL would be faster, with the caveat that on an overloaded server, I wonder if items would get queued if SWIS got bottlenecked.
It would be amazing to get an 'Official' response on this Perhaps from Head Geeks Office Hours (kong.yang adatole patrick.hubbard sqlrockstar)
We would LOVE for you guys to jump on Office Hours to discuss this. The next one is Monday at 1:00pm Central Time. http://www.thwack.com/OfficeHours
See you there!
+1 for the with (nolock)
I've hit a couple of deadlocks caused by the merger with NPM (<11.5) and NCM where the locking on the nodes table interferes with NCM.
in addition, with SQL you can run it in the SQL studio and get the execution plan. SQL is a descriptive/declarative language there is more than one way to solve a problem; the performance of different SQL queries can be more easily tested using that tool.
We need to get a cage match between patrick.hubbard and sqlrockstar on this one.
I'm taking bets and running the concession stand. Kosherhot dogs, popcorn and beer!!
