January 11, 2010 at 12:01 pm
I'm looking at output from Profiler, activiity from our 3rd party ERP application and see a lot:
SET FMTONLY ON
SELECT plt_name from plant where 1=2 <-- the key being the WHERE clause will return nothing
SET FMTONLY OFF
Then they are followed by a similar SELECT:
SELECT plt_name from plant where plt_no = @p2
* The details of the SELECT statements vary, the gist being the WHERE clause.
What in the world is the objective of these statements?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
January 11, 2010 at 12:10 pm
That gets a list of columns that the query will return (format only).
The where 1<2 makes sure the sql returns the columns without having to run the query at all beyond figuring out the columns and datatypes.
January 11, 2010 at 12:27 pm
OK. Thanks for that part.
What's the objective? Doesn't the statement that returns actual rows accomplish the same thing, plus the data? i.e. Why would the program want to get the columns only and then turn around and get the columns and data? It seems like unnecessary overhead on resources.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
January 11, 2010 at 12:55 pm
It's not that expensive... once the data is in ram it's pretty fast.
Also if you want your program to always display exactly the data that the view/query/sp returns it's a good way to do it without having to rerun install on all pcs on the network. Especially usefull if the ERP has programmers come on site to do small tweaks for the users.
It might also be a way to check if user has permissions but I don't think it'd make any difference..
The way this is setup, it might also seem like the app is really fast... from the user's perspective, even if there's a slight perf hit overall.
January 12, 2010 at 6:22 am
There's something I'm not understanding.
I see there are two hits on the HD instead of one. HD access is a bottleneck. (Here comes the part where I'm probably missing something). I would make the one HD request then (1) test the results, i.e. Are there records?, (2) check the data, i.e. Is the data acceptable?, (3) process what needs to be displayed, and (4) display information.
Something you said "it's a good way to do it without having to rerun install". (Hey! Maybe I don't understand something here 🙂 ). The application executables reside on a file server, clients have shortcuts to them. Teach me something, oh wise one. What's the situation/scenario you refer to that requires "to rerun install??
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
January 12, 2010 at 7:34 am
Some ERPs I've worked with are installed client side. That's mostly where I'm coming from.
AS For HDs and format queries, I wouldn't worry too much about this. The hit is going to be in system objects which is 100% in RAM unless you have extremely serious ram issues.
As for HD hit, you might post more details about that and we'll see what we can do.
January 12, 2010 at 8:00 am
Ah. The picture is coming into focus for me. (Let's see if my picture is correct - 🙂 )
Some ERPs I've worked with are installed client side. That's mostly where I'm coming from.
Since we are server side I didn't even think of that. And, now that you mention it, I think some customers do install the files on each PC. So, the programmers are taking that scenario into account. Since the FMTONLY ON Select is in RAM it has negligible impact on us server side customers and makes the client side more efficient.
No! Wait. I think I'm was wrong. Even though the executables are located on the server they are executing on the PC. Therefore, we are client side. Is this correct?
We have more than enough RAM. Nice tidbit to know.
As for HD hit, you might post more details about that and we'll see what we can do.
Not sure what you mean by details - not important at this time to pursue further. Like I said, it's when a lot of users are pounding at the same time that performance degrades and I AM PROBABLY MORE CRITICAL than the users - I just want the best for them and whatever can be done I will do.
It's nice that our SQL Server is virtually, "Set it and forget it." It runs very well and a reboot once in a while clears up the temp space. Good news - we're getting two new servers this year - ERP application File Server and MS SQL Server. As I understand it SQL Server 2007 will be a tremendous jump in disk space and performance over SQL Server 2000.
Thank you very much. I appreciate your taking the time to help me understand.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
January 12, 2010 at 9:50 am
Nothing we can do about point 1 and I can't confirm without going there myself...
For point #2
http://www.codeproject.com/KB/database/DiagnoseProblemsSQLServer.aspx
February 4, 2010 at 1:45 pm
Thanks for the link. I finally set myself down and read it. Since our SQL Server has been virtually "set it and forget it" since being installed in 2004 I'm figuring there's no urgency. That said, I'm trying to be proactive and start understanding DBA Admin 'tasks and responsibilities' to make sure it keeps running well for the users down the road.
I appreciate your time and effort.
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply