January 28, 2008 at 3:29 am
Good morning at all.
Im using a Db on a Sql 2005 with Vb .Net
I want to create a temporary index to use only when my program is running.
Is possible or i can search another solution
thank you very much !!!
Alberto.
January 28, 2008 at 5:05 am
What are you trying to achieve with this temporary index? And why do you want it to only be temporary?
January 28, 2008 at 5:10 am
the database i'm reading is not mine.
but is necessary form me to check which information are stored inside.
I dont want to change something , if i put inside a table a new index.
January 28, 2008 at 5:20 am
That explanation doesn't help me figure out how to help you, unfortunately. What information are you trying to check?
Also, if this DB isn't yours, you need to talk to the person who owns it. By adding an index, ANY index, you are making changes to the table. There are no such things as "temporary" indexes.
You might be able to pull the data into a temporary table and create an index on that, then drop the temp table when you're done. But that's the closest you'll come to a temporary index without making changes to the base table.
January 28, 2008 at 5:52 am
Further, since as you say this isn't your database, if the owner has set up security appropriately, you probably won't have DDL permissions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 28, 2008 at 6:27 am
I understand, but my problem is the table dimension.
This table is very big and my problem have to have fast answer.
I see that inside this table there are no indexes and my question is if I create one tbl to drop at the end of my loop, if there are some changes on the db.
January 28, 2008 at 7:06 am
Yes, there are changes on the DB. Both when you create the index and when you drop it.
Additionally, if you create a clustered index, you're changing the whole structure of the table itself and maybe the location, depending on where you place the clustered index. If the table is that huge, creating an index might take as much time as just running the query without one.
January 28, 2008 at 7:22 am
Ok.....is better i don't use a create !!!!
i make one "Select count(*)" to know if there are some record inside table.
I put one "Where" option but is very very long.........
Which kind of other method i can use ???
January 28, 2008 at 7:32 am
Alberto,
Unfortunately, without more details on what you're trying to pull from the table, I can't assist you further. You need to give more details on what you're trying to accomplish.
January 28, 2008 at 7:32 am
If you simply want to know about existence, you probably want to look at using something like an EXISTS clause, possibly combining it with a TOP 1 clause.
Count(*) is an awful lot of work, just to check for existence.
If you could give us some data specifics (what the tables look like, what you're trying to check for), we can try to give you some code examples that apply to your case.
----------------------------------------------------------------------------------
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?
January 28, 2008 at 7:46 am
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.
January 28, 2008 at 8:08 am
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?
January 28, 2008 at 9:47 am
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(*)
January 28, 2008 at 10:38 am
And if i create a new empty table and i try to link one index to the big table ????
is possible ?????
January 28, 2008 at 10:58 am
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.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply