|
You could look at the constaints on the foreign key. Does it allow duplicates, does it allow nulls?
|
|
|
|
|
I have one procedure that's timing out when it's called through the user interface, yet when I run it on the database itself it takes 7 seconds at most and the time out is set to 60 seconds. Every other procedure I can run works just fine through the UI, including those that take considerably longer.
When run on the server itself it peaks for a moment at about 20% CPU use, and is over within 7 seconds maximum. Execution plans aren't showing me anything interesting, I'm not seeing anything special on any of the diagnostic tools I have available.
Running it through a function call it makes a nice Plateau at a bit over 20% CPU usage, and sits there until it times out. Nothing I can check other than that is significantly different when it's called programmatically, at least nothing any different than any other procedure.
Any thoughts on what this could be? Or do I finally have a case to get them to upgrade the server so I can run SQL Profiler on it?
|
|
|
|
|
Erland Sommarskog has an article[^] about how to troubleshoot this kind of problems.
|
|
|
|
|
Just a thought. Do you use the same credentials to run the SP from the UI and on the Database directly? If not, you may want to investigate this option.
|
|
|
|
|
Check that it is not a deadlock condition. I believe SQL server will terminate (pick a victim) and terminate a session so that others can continue.
|
|
|
|
|
Do you have return statement somewhere in the code?
RETURN by itself I think is okay but RETURN someValue may not
|
|
|
|
|
Did u check the amount of data the procedure is returning. In case the procedure is returning too many rows of data, then that might be the cause of timeout when you run the procedure programmatically. But when you run the same procedure from database itself which i guess is Sql Management studio, only top 500 or 1000 rows would be returned depending on the SSMS setting. Hence the result comes up without time out.
|
|
|
|
|
I was working on a chunk of SQL in management studio. Finally got it to return the results I wanted. Then I converted it to a stored procedure, where it promptly stopped working. Upon tracking the bug down I found that SQL was handling Null variables differently in dynamic SQL vs my stored procedure.
I had this bit of code:
if (@Replace != '')
set @MaterialNumber = @Replace
In dynamic SQL if @Replace was null, we did not enter the if block. In a stored procedure when @replace was null we entered the if block and set @MaterialNumber to null. 
To the best of my knowledge, this should never happen. Is this a bug in SQL Server or is there a setting hidden somewhere that could account for this difference?
|
|
|
|
|
Whenever I check for NULL, I avoid doing so with comparison operators. There should be some IS NULL check available to use instead. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
That seems correct to me, but I'm unsure what you mean by "dynamic SQL".
|
|
|
|
|
By dynamic I meant SQL run directly in Enterprise Manager. When run in a stored procedure the SQL code behaved one way, when run directly in Enterprise Manager it behaved another way. This is the first instance I've discovered where there was a difference between the two.
|
|
|
|
|
In SQL-server '' is not the same as null, it's a zero length string.
|
|
|
|
|
May not be the best way but try:
IF (ISNULL(@Replace, '') != '')
SET @MaterialNumber = @Replace;
|
|
|
|
|
My concern wasn't so much the null handling, that's easy enough to work around.
I'm more concerned by the fact that the SQL code behaved differently when it was run in a stored procedure VS when it was run directly in Enterprise Manager. This is the first instance I've discovered where there was a difference.
|
|
|
|
|
thrakazog wrote: In dynamic SQL if @Replace was null, we did not enter the if block.
Create a stored procedure that demonstrates exactly that and then post it.
|
|
|
|
|
Gaaaah, I finally found the cause of the problem. I apparently created my original sproc with "SET ANSI_NULLS OFF". By default queries in management studio runs with ANSI_NULLS ON. DOH![^]
For a simple example of the sproc I was playing with:
create PROCEDURE [dbo].[Bob_Test]
@MaterialNumber varchar(18)
AS
DECLARE @Replace varchar(18)
SELECT @MaterialNumber
IF (@Replace != '')
SET @MaterialNumber = @Replace
SELECT @MaterialNumber
When I call this with ANSI_NULLS OFF
Bob_Test '123456' my results show 123456 from the first select in the sproc, and NULL for the select after the if statement.
With ANSI_NULLS ON both selects return 123456. My faith SQL Server has been restored. Tune in next week for more inept coding... 
|
|
|
|
|
More of a theory question than anything. I have a website where people who register fall under two different user types, say attorney and client for example.
If a person registers as a lawyer, they fill out a lawyer profile. If they register as a client, they fill out a client profile. The two profiles are different.
So I am trying to figure this out from a database standpoint. Do you think the best bet is a three table setup?
User
------
ID
Email
Password
User_Type (1 for lawyer, 2 for client)
Lawyer
-------
ID (AI field)
User_ID
First
Last
etc
Client
--------
ID (AI field)
User_id
First
Last
Violation
etc...
Or I could do a two table with just Lawyer and Client and check both tables at login time? It would be more code intensive but less data. What is a good approach here? Open to suggestions.
Cheers, --EA
|
|
|
|
|
Turn it on it's ear, create a person table and attach an attribute for role, use a many to many link table to cater for a lawyer who is also a client!
Person
PersonID
lnkPersonRole
LinkID
PersonID
RoleID
Role
RoleID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That is a good solution, thank you. In regards to the profile aspect, how would you handle role specific attributes? Say, I want to store a lawyer's license number, or a client's height and weight?
|
|
|
|
|
You have 2 real choices, add each attribute as a column in the person table (accepting that a lot will be blank) or add another table of person attributes and only link the valid attributes to each person record.
AttrType
AttrTypeID
Attr [Licence No]
PersonAttr
AttrID
AttrTypeID
AttrValue [123456A]
The attributed solution is technically the most correct method, it is also the most complex and that complexity can cause issues. The column method means that every time you need to add an attribute you have to touch the code. It then becomes a business decision which way to go.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Seems like another good answer. What is the purpose of the AttrId column in the PersonAttr table? A simple primary key? Or is it foreign key for another table?
|
|
|
|
|
EVERY table needs a primary key (in an OLTP database) so the AttrID is an IDENTITY field and is the primary key.
Note the format (int) is a convention, some like GUIDs but I can't stand them. I also insist the primary key field is the FIRST field added to the table and is the table name + 'ID' so it should have been 'PersonAttrID'
When you have 100s of table you do not want to have to chasing down the name of the PK field for each.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am with you on the guid issue, unnecessarily complicated. I see no reason to go further than an identity field. This project is MySql and CakePHP, so it dictates it's own naming convention for tables and columns and such. Well, you can get around it but it requires unnecessary code to get around.
Anyways, thank you for the guidance I am going to go with person table, attribute table and then third table that has id, person_id, attr_id, and attr_value. Is this really a true many to many relationship, adding the attr_value column to the joining table?
|
|
|
|
|
One more question about this: for fields that are common to all role types and mandatory, would you encourage adding those to the person table? For example, email address, age?
|
|
|
|
|
Normally yes, at least for data that's directly connected to the person, such as age, weight and length and such.
But while an email address might be personal, it might also be connected to the work role, and a person might also have more than one email address. And so on.
It depends on the purpose of your application, and how it might change in the future. Always plan ahead.
<edit>fixed typo</edit>
modified 21-Jan-12 17:09pm.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
