cost of select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cost of select query

Author

Topic

vishalg
Starting Member

29 Posts

Posted - 2009-01-11 : 18:22:17

Hello,

I am using several select statements but one single table in a stored procedure. But this procedure will run over a million records. So that means the select statements will run over several million records. Can this hamper the speed of my engine?

Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-11 : 18:47:23

Yes it can, but with proper indexing, database design and maintenance jobs, you should be able to query it quickly as long as you aren't looping through the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vishalg
Starting Member

29 Posts

Posted - 2009-01-11 : 20:39:34

Thanks Tara for the solution.

Also can you help me because I want to match values between two tables. But I want to match rows of one table and columns of one table. Thus I cannot use "tablediff". So I was thinking of copying data from each table into one variable at one time and then match them.

I know this is a lame approach but since a newbee I cannot think of any other idea. Can I get help on that?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-11 : 20:41:14

Please provide a data example as your description is unclear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vishalg
Starting Member

29 Posts

Posted - 2009-01-11 : 20:48:57

The targeting engine requires matching required demographics stored in one column as
Example: "Male", "New York", age

And I have to match these values with a table which have customer details in columns like SEX, LOCATION, DATE OF BIRTH

Thus, I have to find the customer records which match the given demographics.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 21:10:26

quote:
Originally posted by vishalg

The targeting engine requires matching required demographics stored in one column as
Example: "Male", "New York", age

And I have to match these values with a table which have customer details in columns like SEX, LOCATION, DATE OF BIRTH

Thus, I have to find the customer records which match the given demographics.

Can you provide sample data and expected output?

Can you provide sample data and expected output? It is still not clear.

vishalg
Starting Member

29 Posts

Posted - 2009-01-11 : 21:41:22

These are the two tables i want to match. As you can see in the example table "targetdemographics", the required demographics are
"MALE", "NEW YORK", "30-50" which are given in one column.
The other example table "clinicalpatient" gives patient information in different columns as "PATIENT_CITY", "PATIENT_SEX", "PATIENT_DOB"

Thus, if these demographics match the patient information then i would like to Inset the paitent id on other table.

Can this be done in sql server 2005?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-11 : 23:43:40

quote:
Originally posted by vishalg

These are the two tables i want to match. As you can see in the example table "targetdemographics", the required demographics are
"MALE", "NEW YORK", "30-50" which are given in one column.
The other example table "clinicalpatient" gives patient information in different columns as "PATIENT_CITY", "PATIENT_SEX", "PATIENT_DOB"

Thus, if these demographics match the patient information then i would like to Inset the paitent id on other table.

What you like to insert to which table?Can you clarify?

Can this be done in sql server 2005?

vishalg
Starting Member

29 Posts

Posted - 2009-01-12 : 00:08:12

Hello,

I would like to insert the id of the "matched" patient record into another table which has one single column called "MATCHEDID". So the inserting in the table is a later part. But what i would want to know that what approach i thot of copy each of the column data into variable and then matching them individually according to pairs is feasible or no? Also, is there any other better tool, like tablediff, i should try to read upon?

also, does this type of querying work in sql? this is what i was initially planning to do.. (my lame approach)

SELECT @TEMP=targetdemographics from nm_targetdemographics where targerdemographicstype="SEX"

SELECT @TEMP1 PATIENT_SEX from cdb_clinicalpatient where PATIENTID="1"

if @TEMP=@TEMP1
copy patientid from clinicalpatient table to matchid column in matchtable

something similar to this. but as i am reading more, i dont think this can be done.

vishalg
Starting Member

29 Posts

Posted - 2009-01-12 : 00:17:22

the code mentioned above was basically to copy the data in the column in a variable to match. and then put a basic if condition. I have no clue can this is be done in sql stored procedures. And if this works.. i have still a lot more to go. I'm trying to divide and conquer.. but I guess I have too many sql syntax issues..

vishalg
Starting Member

29 Posts

Posted - 2009-01-12 : 01:02:49

ok guys.. I corrected my code a little bit and I think I found something to find a match.

SELECT @TEMP=TARGETDEMOGRAPHICS from nm_targetdemographics where TARGETDEMOGRAPHICSTYPE='SEX'
SELECT @TEMP1=PATIENT_SEX from cdb_clinicalpaitent where PATIENTID='1'

if @TEMP=@TEMP1
BEGIN
PRINT @TEMP
END
ELSE
BEGIN
PRINT @TEMP1
END

But this is for just one demographic and one patient. I need to do these for "n" demographics and million patients. Can I optimize this process in any other way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 02:42:56

is this what you want?


SELECT TARGETDEMOGRAPHICS 
from nm_targetdemographics t
where TARGETDEMOGRAPHICSTYPE='SEX'
AND EXISTS(SELECT 1 from cdb_clinicalpaitent where PATIENTID='1'
AND PATIENT_SEX =t.TARGETDEMOGRAPHICS)
UNION ALL
SELECT PATIENT_SEX 
from cdb_clinicalpaitent c
where PATIENTID='1'
AND NOT EXISTS (SELECT 1
from nm_targetdemographics t
where TARGETDEMOGRAPHICSTYPE='SEX'
AND TARGETDEMOGRAPHICS=c.PATIENT_SEX 
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:34:40

Yes, it can be done in sql server.
Use PIVOT to make two denormalized derived tables and compare them.

Or use UNPIVOT to make two normalized derived tables and compare them.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources