Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Create and use temporary INDEX Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 7:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
my problem is to know if exist inside my db at least one or more rows with this command

Select count(*) from tabelname where field1 = val1 and field2 = val2

Unfortunately does not exist one index with inside my field1 and field2.

Post #448314
Posted Monday, January 28, 2008 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:32 PM
Points: 7,158, Visits: 15,265
Again - Count(*) is overkill for a scenario like that. You don't care how many there are, you just want to know there are some matching records.

So instead of
select * from table1
where (select count(*) from table2 where table1.field1=table2.val1 and table1.field2=table2.val2) >0

Try using this (which usually runs faster)
select * from table1
where EXISTS (select top1 *from table2 where table1.field1=table2.val1 and table1.field2=table2.val2)

But still - this would run a LOT faster if you simply asked the owner of said DB to add in the index you need. Is this a vendor DB or something that prevents you from even asking?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #448337
Posted Monday, January 28, 2008 9:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
is not for me........
i have to read only one table.

for me is necessary understand if there are some record inside one table.......and i think the only way is to make Select count(*)


Post #448431
Posted Monday, January 28, 2008 10:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
And if i create a new empty table and i try to link one index to the big table ????
is possible ?????
Post #448462
Posted Monday, January 28, 2008 10:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
You can't link indexes between tables. An index only ever goes on 1 table only.

The only way to link tables is with a foreign key constraint and that's not what you want to do.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448481
Posted Tuesday, January 29, 2008 2:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
Ok !!!

I understnad....or i change situation creating one new table...or i use "Select count(*)" and wait

right ?
Post #448738
Posted Tuesday, January 29, 2008 4:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
Like Matt, I would not do a Select Count(*). If you're only looking for the existence of the records instead of caring about how many records there actually are, use his code instead. It's much easier.



Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #448754
Posted Wednesday, January 30, 2008 3:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 2,879, Visits: 3,228
Alberto, is there anyone in your office wearing a blue shirt?

When you answer this, do you count all the people who are wearing blue shirts before answering yes or no. Or do you see just one person wearing a blue shirt and answer yes. Which is quicker for you to do?

The query you are describing in your postings seems to be counting the total number of blue shirts just to decide if at least one person has a blue shirt.

Take another look at the previous posts in this thread. If your query just needs to know a true or false answer, look at the suggestion on how to make this work.


Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #449248
Posted Wednesday, January 30, 2008 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
Ed,

That is an *excellent* way of rephrasing the question! Thanks for that. @=)


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #449264
Posted Wednesday, January 30, 2008 4:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 11, 2010 7:57 AM
Points: 35, Visits: 110
Thank you very much Ed !

Good answer !!!
I open my eyes !!!!!!!!


thank you for all your support !

bye
Alberto.
Post #449272
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse