DB Index optimization

[vass0922]

I have a whopper of a table that is roughly 10-11 GIGS
(one table) in sql server.
No I can't parse out the data into more tables because its not really a relational database its just used to track netstat information.

Ok my problem is I'm trying to run a query to clean up the data but its going god awful slow.

I put an index on the columns I'm using but that didn't help so I'm wondering if its because I concatenate the columns together in the query.

My query
select connectionid
from connections
where servername + RemoteAddress + LocalPort = 'servernameipinfolocalport'

Now my index is...

CREATE
INDEX [idx_CleanupIndex] ON [dbo].[Connections] ([ServerName], [RemoteAddress], [LocalPort])
WITH
DROP_EXISTING
ON [PRIMARY]

Would it help if I changed the index to

CREATE
INDEX [idx_CleanupIndex] ON [dbo].[Connections] ([ServerName] + [RemoteAddress] + [LocalPort])
WITH
DROP_EXISTING
ON [PRIMARY]

I don't just want to whack it and give it a shot because it will take roughly 20-30 mins to create the stupid index and HD space is quite limited (because of this Db lol)

Now this is on a 4 proc server with 1.5 gigs of RAM.. this should NOT take that long!!! lol

This thing has roughly 63,952,419 rows of data so yea it will take some time but jeeeeez.
SQL Server 2k isn't THAT pathetic is it?!

[vass0922]

Ok so
CREATE
INDEX [idx_CleanupIndex] ON [dbo].[Connections] ([ServerName] + [RemoteAddress] + [LocalPort])
WITH
DROP_EXISTING
ON [PRIMARY]

doesn't work
It doesn't like the Pluses

If I do a query of
select connectionid
from connections
where servername = 'servername' and RemoteAddress = 'ipinfo'
and LocalPort = 65465 it comes up real fast like it should!
But concatenated its too slow
*sigh* well if anybody has any ideas, I am gonna see if I can restructure my stored procedure a bit.

[vass0922]

I was able to re-structure the queries and the Stored Procedure to take better advantage of the Index so it may work out after all. Works MUCH better now
however, if you know how to do the above (index for concatenated columns) would still be quite interested in that knowledge

[Creosote]

I was going to suggest trying an SP. Do you always create your indexes in code? I just make them in the designer....

[vass0922]

I created them in the designer but all that does is write the sql for you
I'm out of habit so can't create them by code by myself anymore lol

And even when I ran it as a compiled SP it only went through 14k rows in 18 hours lol
NOT GOOD!

I changed my methodology a little, so now I populate a table with Id's that I can delete out of the table and already up to 176k
only 63.6 million left LOL