|
I've got old code in MFC which I've got to update. The old code uses DAO and I'm interested to update to ADO.NET. According to what I've read, it is desirable to have DataSet objects where each DataSet object addresses a single table. Then DataRelation is used to make the connection between tables. (Up until now I made SQL queries which did a join between 2 tables.)
My question now is how do I do things efficiently?
Suppose I have 2 tables, patients and studies. Patients has the name, Patient ID, etc. Studies has Patient ID as a foreign key and other information such as date etc.
To do things efficiently I need to limit the number of records so as not to dump the entire contents of the database. Suppose I look for a patient whose name begins with "abc". Then the query of the patients is efficient because I do something like
SELECT * from patients where name like "abc"
This gives me a list of patients with IDs. This can be a long or short list, which I don't know up front.
The question is: how can I make an efficient query on the studies table, i.e. limit the number of rows?
Also I can ask the opposite question. Suppose I want to query on the basis of date. Then I efficiently query the studies table, but how do I efficiently query the patients table?
Thanks for your help,
Ilan
|
|
|
|
|
IlanTal wrote:
According to what I've read, it is desirable to have DataSet objects where each DataSet object addresses a single table.
No, you would only have 1 dataset, but it would have multiple DataTables in it. You fill the tables with data using one or more data adapters. Then you would add a DataRelation to your DataSet, relating the 2 tables.
So, if you get the patient records with:
SELECT Patient.* FROM Patient WHERE name LIKE 'abc'
You can use a join to fill the Study table, but do not retrieve any fields from the Patient table:
SELECT DISTINCT Study.*
FROM Study
INNER JOIN Patient ON Study.PatientID = Patient.PatientID
WHERE Patient.name LIKE 'abc'
Then add a DataRelation to your dataset:
MyDataSet.Relations.Add("PatientStudies",MyDataSet.Tables("Patients").Columns("PatientID"), MyDataSet.Tables("Studies").Columns("PatientID"))
You could fill the dataset with 2 queries, or you could use a stored procedure to run both queries in a single call to the server (return multiple result sets if your database supports it).
IlanTal wrote:
Suppose I want to query on the basis of date. Then I efficiently query the studies table, but how do I efficiently query the patients table?
SELECT DISTINCT Patient.*
FROM Patient
INNER JOIN Study ON Patient.PatientID = Study.PatientID
WHERE Study.Date BETWEEN @FirstDate AND @SecondDate
|
|
|
|
|
Thank you Scott for your input. Now is the time I have to get things straight, BEFORE I write any actual code.
I see where you are going and will use your advice. I still have one question to your answer. You wrote:
Scott Serl wrote:
SELECT DISTINCT Study.*
FROM Study
INNER JOIN Patient ON Study.PatientID = Patient.PatientID
WHERE Patient.name LIKE 'abc'
The question is: what does DISTINCT do for you? What would be different without it? (In the database all the studies are distinct since I have a unique key on one of the fields.)
Thanks,
Ilan
|
|
|
|
|
It depends on the problem domain. Because I am joining to the Patient table, I may have more than one patient in the same study. DISTINCT will eliminate the replicates and give only one entry for each study.
|
|
|
|
|
Thanks for the reply. Now I understand what the problem was which concerned you. In my case it goes the opposite direction. A given patient may well have more than one study (AIDS being the classic case), but a study is done on a specific patient.
In any case I understand what you were driving at.
Thanks,
Ilan
|
|
|
|
|
Suppose i create sp
CREATE PROC myProc @criteria1 int,
@criteria2 nvarchar(50) = ''
AS
SELECT * FROM myTable
WHERE column1 = @criteria1
AND
column2 = @criteria2
GO
---------------------------
my question is in case the parameter @criteria2 is empty (equal '')
i want the select will ignore this criteria
so it perform like:
SELECT * FROM myTable
WHERE column1 = @criteria1
-----
the same with other criterias.
I can have a lot of parameters and and some of them can be empty or not.
Thanks.
------------------------------------
To study, study and only to study
|
|
|
|
|
You can use conditional logic in your procedure, but you may want to consider why you've got empty parameters coming into a stored procedure - there are valid reasons, but it's nice to try to avoid it. You can also set defaults on parameters to sidestep having to use conditional logic in some cases.
Example:
-- These variables would typically be input params<br />
declare @param1 nvarchar(10)<br />
declare @param2 nvarchar(10)<br />
set @param2 = 'Value1'<br />
<br />
-- These variables just hold the length of the params<br />
declare @lenP1 int<br />
declare @lenP2 int<br />
set @lenP1 = len(@param1)<br />
set @lenP2 = len(@param2)<br />
<br />
-- Outputs the state of the params<br />
if @lenP1 > 0 and @lenP2 > 0<br />
select 'Both parameters were provided.'<br />
else if @lenP1 > 0 and (@lenP2 = 0 or @lenP2 is null)<br />
select 'The first parameter was provided but the second was omitted'<br />
else<br />
-- This statement will be executed.<br />
select 'The second parameter was provided but the first was omitted'
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
Instead of defaulting the value to an empty string you can leave it as null
Then you can do a select like this
CREATE PROCEDURE MyProcedure
@criteria1 int,
@criteria2 varchar(50)
AS
SELECT *
FROM MyTable
WHERE column1 = @criteria1
AND (@criteria2 IS NULL OR column2 = @criteria2)
GO
The key here is the AND... line. What it says is that if @criteria2 is null then the clause is true. If @criteria2 is not null then column2 must equal @criteria2 for the clause to be true. In an OR condition one or both sides must be true for the whole to be true.
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Yes, thanks it's help. However the statement that u wrote:
AND (@criteria2 IS NULL OR column2 = @criteria2)
possible write as:
WHERE column1 = @criteria1
AND (column2 = ISNULL(@criteria2, column2))
------------------------------------
To study, study and only to study
|
|
|
|
|
If you want you can write it that way too. However, I'd think that using a function like this wouldn't be as efficient (but SQL Server often surprises me with what it can do efficiently and what not) - so, if that is important to you then you should consider writing both versions and running them through the query analyser to see which generates the better execution plan.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
generates the better execution plan, what do u mean by saying it?
how to check execution plan in the query analyser.
Thank u
------------------------------------
To study, study and only to study
|
|
|
|
|
|
thanks i got it,
I have another question to u: i have stored procedure that perform select ... from ....
also this procedure return value (e.g. return 0)
if i call to the sp by EXECUTE i got the result of my sp(result of query.)
if i do:
DECLARE @res int
EXEC @res = My Stored proc
PRINT @res
i got only what the sp return.
how to get the return value and the query result(set result) both?
------------------------------------
To study, study and only to study
|
|
|
|
|
Hi Guys,
Please give advice.
I have a program that attached files. What I do is after pressing the save button it saves the entire path of the file to my table plus of course the key (attach with columns userid,fileloc,filename) regarding the physical file I saved it in a pre defined directory in our server which is related to my WEB PAGE PROJECT.
Question: What is the advantage of saving the entire file into a SQL Server rather than what i've mentined above?
/Dabuskol
|
|
|
|
|
That's kind of a hard question to answer without any other context. It seems like, if you have to ask what the advantages are, it's probably not something you really need to worry about just yet.
I guess there are obvious differences, but it's hard to say if they're advantages without knowing what it is you're trying to accomplish.
If the application may be sold to others, or there are potential file system sharing issues (networked users may need access to files), then saving the files to the database may be an advantage.
If file versioning is something you want to implement, databases offer some advantages over the file system alone.
Backups may be arguably simpler (or at least require less third-party software in many cases).
There are potential security advantages in storing files in a database - it's almost impossible to execute a malicious script if it's stored in a BLOB...
There are no doubt other, more situation-specific advantages to storing files in a database versus the file system, but there are also potential disadvantages.
Accessing a file stored in a database is seldom as fast as accessing one from the file system.
Sharing copies of a file stored in a database certainly takes more steps than sharing one on the file system.
Storing files of any size in a database can cause significant bloat, etc.
Good luck.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
CREATE PROCEDURE proname
@param int,
AS
SELECT TOP @param field1, field2, fiedl3
FROM tablename
GO
When executing the stored procedure i above, i'm reported that error happens at @param
How could i pass parameter in 'SELECT TOP' statement above
Thanks
|
|
|
|
|
You aren't allowed to use a parameter with TOP in that way.
You could, however, concatenate a sql expression using the parameter, like:
declare @param int<br />
declare @sql nvarchar(100)<br />
set @param = 10<br />
<br />
set @sql = 'select top ' + cast(@param as nvarchar(4)) + ' * from FooTable'<br />
<br />
exec (@sql)
Does that help?
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
How i can simplify this statement to better performance?
T is a table .
Column ID is not unique.
SELECT DISTINCT ID FROM T
WHERE
ID IN (SELECT ID FROM T WHERE TypeID = i)
and ID IN (SELECT ID FROM T WHERE TypeID = j)
.
.
.
and ID IN (SELECT ID FROM T WHERE TypeID = k)
and ID NOT IN (SELECT ID FROM T WHERE TypeID NOT IN (i , j , ... , k ) )
|
|
|
|
|
If you are looking for all IDs that have a record with a TypeID of i and j and k and at least one other TypeID then I think this would work:
SELECT
DISTINCT a.ID
FROM
T AS a
INNER JOIN
T AS b
ON (a.ID = b.ID)
INNER JOIN
T AS c
ON (a.ID = c.ID)
INNER JOIN
T AS d
ON (a.ID = d.ID)
WHERE
a.TypeID = i,
b.TypeID = j,
c.TypeID = k,
d.TypeId NOT IN (i,j,k)
|
|
|
|
|
There is an error in your statement.
Are u sure this statement has a better performance?
Any Idea?
|
|
|
|
|
The error might be in the engine - I use MS SQL 2000 - or it may be that there is typo since I didn't create any test tables.
Only examining the execution plan between the two queries and testing will tell you which one is faster. Indexes also help.
My experience is that IN (or NOT IN) statments tend to generate more table scans whereas JOIN makes use of indexes better. Given your set of criteria, an index on [TypeID, ID] could generate a faster query.
|
|
|
|
|
Try this:
select ID from
(Select ID, sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 1 else 0 end) as InList,
sum(case when TypeID in ('I', 'J', 'K', 'L', 'M') then 0 else 1 end) as NotInList
from t
group by ID) as foo
where inlist > 0 and NotInList = 0
Tony Lewis
SQL "Guru"
XAKTsoft, Inc.
|
|
|
|
|
I used the following method a few years back:
SELECT ID FROM T
WHERE TypeId IN (i, j, k)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3 Where "3" is the number of terms in the "IN" clause. To handle your "NOT IN" requirement, you could use the following:
SELECT ID FROM T
WHERE ID IN (SELECT ID FROM T
WHERE TypeId IN (i, j, k)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3)
GROUP BY ID
HAVING COUNT(DISTINCT TypeId) = 3I don't know how fast this will run against your chosen database engine. Under SQL-Server, I would normally expect the following indexes to help:
Clustered index on T (ID, TypeId)
And possibly:
Nonclustered index on T (TypeId)
Hope this helps.
Regards
Andy
|
|
|
|
|
i just want to save time in a field got type is datetime, but every time i insert time into the field, SQL SERVER automatically add day to that field.
Any way to solve this problem? Thanks in advance
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
