|
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run.
Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id.
It didn't even make a difference when I set the proc to run WITH RECOMPILE.
Can someone give me a clue of what is going on here? I'm lost.
|
|
|
|
|
What are you using to filter the user permissions, I hope you have a table that links the GL accounts to a userid/group.
When the admin is used there is no filter so instant response, if you filter the results SQL has to do a scan of the table to locate the GL accounts, put and index on the linking fields (userid, accountid etc) in the various tables. There will almost certainly be indexes on the primary tables (userid on user table, accountid on GL table) but the linking table may not have any indexes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Actually, whether the user is admin or not, the security mechanism is the same. They have permissions to access certain branches, and those branches have access to GL accounts.
I checked the indexes and the estimated execution plans and didn't see anything strange. After some fighting with the system, it appears that all I had to do was declare a variable inside the stored procedure, set it to the value of one of the parameters and then use it in my select clause. For whatever reason, this fixed the issue (and so far, it hasn't popped up again). I'm guessing that it somehow managed to cache an execution plan that was inappropriate for the data it had.
It's still very strange.
|
|
|
|
|
Without seeing the code for the stored proc it's hard to know what the problem is.
Checking the indexes is good advice. If that doesn't work and you're still confused, you could try looking at the execution plan for the stored proc to see what it's actually doing. The SQL Server Query Analyzer lets you do this. Once you can see what the query is doing, you should have a better idea of how to fix it.
|
|
|
|
|
Maybe This[^] article can help.
Wout Louwers
|
|
|
|
|
I am a newer to database of SQL.
I want to create a website which mainly contains a table, wich may contains 20,000 items.
I want to know that whether due to the large table, then perhaps the speed of the website will be very slow for the user to request some
information from this table? if so, if I divide this table into several sub tables, then it would become faster?
secondly, between the following two techniques, which one should be better choice for my applicaiton (I am using ASP.net):
1: using the new ADO.NET data source controls, which are tightly integrated with the GridViewand DetailsView controls in ASP.net.
2: involves using the SqlDataAdapter class in conjunction with the DataTable,DataView, andDataSet classes.
any expert can give me one hint?
thank you!
|
|
|
|
|
20,000 items in a table is not terribly large for a database engine, so you should be fine with response time. The main thing to remember is that a good design will give the best performance. You should research topics like "database normalization and design". I would only split the data into sub tables where it makes sense. For example you may have a customer table which contains the customer ID and address and an Orders table which refers to the customer number; this way you don't store the customer address for each order created. (This is part of good database design)
Relative to the two techniques you mention,
1: using the new ADO.NET data source controls, which are tightly integrated with the GridViewand DetailsView controls in ASP.net.
2: involves using the SqlDataAdapter class in conjunction with the DataTable,DataView, andDataSet classes.
Look at the following article by Microsoft:
http://msdn.microsoft.com/en-us/library/aa581778.aspx[^]
Look around the MSDN site for all kinds of helpful "How To" articles and videos.
|
|
|
|
|
I think Davids answer is excellent, except 20,000 rows in a SQL database is miniscule, we have systems with 20 million rows. The trick is not returning all 20k rows to the user, this is the UI design, only show them the minimum number of rows to meet their needs.
Check out tuning and index design, get to know your database.
As for using the MS tightly integrated adapters and strongly typed datasets I loathe these constructions, the developer (new) has no real understanding of what is happenning. A large number of post on these forums are cries for help from new devs who cannot customise their data because they are using a wizarded widget they learnt in the tutorials. By all means use the tutorials but remember they are only the FIRST step, you are going to need a deeper knowledge to become proficient.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Guys ,
Is there any script that i can run on the database server so that i can retreive all the Computers(IP addresses) that tried connect to the database?
Please advice.
Many thanks,
Regards,
kibrom
|
|
|
|
|
I think you can't retrieve client IP's trying to connect to your DB.You could query net_address from sys.sysprocesses which is unique identifier for the network adapter on the workstation of each user ,but how to refer it to user IP I don't know.
|
|
|
|
|
Hi all,
how to check the existance of user name from Sql server with case sensitivity.
|
|
|
|
|
|
I would like to change my default port 1433 for remote connection to some another port? What port number can give as another port? Is there any rule? How to change the default port?
Iam using sql server 2008.
Any Ideas and Guidences?
Thanks
|
|
|
|
|
From SQL Configuration Manager, Just need to change the tcp/ip port number as anyone new number.
Let it be useful to someone like me.
|
|
|
|
|
I Want to display more than one rows data in just one row.
My Data is:
Code Name
50141 abc
50141 pqr
I want Following Data in result.
Code name
50141 abc,pqr
Please Give me a solution.
Thanks & Regards,
Fazal
|
|
|
|
|
Create Function to return the all names(seperated by comma) for a given code.
Create FUNCTION [dbo].[GetNamesById]
(
@code int
)
RETURNS varchar(max)
AS
BEGIN
declare @result varchar(max)
select @result = COALESCE(@result + ',', '') + [name]
from tblCode
where code = @code
return @result
END
GO
now use following query:
select code,dbo.GetNamesById(code) from tblCode group by code
above query will give output as:
Code name
50141 abc,pqr
|
|
|
|
|
I have two tables that share two columns:
Table 1:
Name: EMR.mmPatientAdmissionSymptoms
Columns: PatientID, AdmittanceID,SymptomID
Table 2:
Name: EMR.mmPatientAdmissionSymptomsOther
Columns: PatientID, AdmittanceID,Symptom
EMR.mmPatientAdmissionSymptoms.SymptomID is an integer whilst EMR.mmPatientAdmissionSymptomsOther.Symptom is nvarchar(MAX).
Now, I want to pull all the data in both tables, where the data in either table not existing being null. That should be a straight full outer join, right?
SELECT
pas.PatientID, pas.AdmittanceID, pas.SymptomID, paso.Symptom
FROM EMR.mmPatientAdmissionSymptoms as pas full outer join
EMR.mmPatientAdmissionSymptomsOther as paso
on pas.PatientID = paso.PatientID
AND pas.AdmittanceID = paso.AdmittanceID
But it doesn't; It seems to preform a cross join. Out of frustration I tried left, right, cross and inner joins just to see if I'm on the right track or not. They ALL give the same result
Scripts if you want to try this: (altered slightly so you don't have to remove the FK and Schemas)
-- Table 1
CREATE TABLE [mmPatientAdmissionSymptoms](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[SymptomID] [int] NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptoms] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[SymptomID] ASC
)
--Table 2
CREATE TABLE [EMR].[mmPatientAdmissionSymptomsOther](
[PatientID] [char](10) NOT NULL,
[AdmittanceID] [int] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Symptom] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_mmPatientAdmissionSymptomsOther] PRIMARY KEY CLUSTERED
(
[PatientID] ASC,
[AdmittanceID] ASC,
[ID] ASC
)
--ID is an Identity field so that I can differentiate between one entry and the other for each patient at each admittance
Results are returned as:
PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 This is a symptom
1234 1 3 This is a symptom
Table 1:
PatientID AdmittanceID SymptomID
==============================================
1234 1 1
1234 1 3
Table 2:
PatientID AdmittanceID ID Symptom
====================================================================
1234 1 1 This is a symptom
One final note, there are no FK relations between the two tables
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Are you wanting to relate/compare the tables or are you in the end trying to merge the tables?
|
|
|
|
|
More like merge them but with the repeated columns removed (PatientID & AdmittanceID).
In a way, I'd want results of the query to be as such: (based on the tables given in the OP)
(Table1) (Table1) (Table1) (Table2)
PatientID AdmittanceID SymptomID Symptom
============================================================
1234 1 1 NULL
1234 1 3 NULL
1234 1 NULL This is a symptom
Makes sense?
[Edit] I just realized that the tables aren't very clear. Try this:
Table 1
| PatientID | AdmittanceID | SymptomID | | 1234 | 1 | 1 | | 1234 | 1 | 3 |
Table 2
| PatientID | AdmittanceID | ID | Symptom | | 1234 | 1 | 1 | This is a symptom |
Wanted result
| PatientID | AdmittanceID | SymptomID | Symptom | | 1234 | 1 | 1 | NULL | | 1234 | 1 | 3 | NULL | | 1234 | 1 | NULL | This is a symptom |
What I get:
| PatientID | AdmittanceID | SymptomID | Symptom | | 1234 | 1 | 1 | This is a symptom | | 1234 | 1 | 3 | This is a symptom |
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
Would a union not make more sense than a join for this scenario?
|
|
|
|
|
I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!
Thanks 
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
You can't use an outer join in this scenario because outer joins return nulls where there is not a match in the other table; You have matches.
A union query would be more appropriate in this case.
|
|
|
|
|
I was sorta pressed for time so I physically merged the two tables on the database but I'll be certain to try it again next time!
Thanks for the explanation though. But I thought Outer joins will return nulls because there was no corresponding columns that had a value (of course, now that I type this I remember that all this is row based).
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
|
hi i have a table with following schema
MsgID SystemID Msg Date(YYYY-MM-DD)
1 1 "" 2009/01/02
2 1 "" 2009/04/03
3 1 "" 2009/05/02
4 2 "" 2009/01/01
I want to identify the SystemIDs which has messages (column : Msg) generated with a Time difference greater than 3 months
In the given table its systemID "1" . ( logged first message on 2009/01/02 and after 3 months 2009/04/03).
please help to get a proper query
My small attempt...
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
