| |
| |
| |
|
Page: 1 2 3 4 5
Comments:
<0> you have to check every value
<0> for substring
<1> true
<1> yuppie you have any comments?
<0> although
<0> mssql might be smart enough
<0> to know to use indexes when it's "text^"
<0> err...you know hwat i mean
<2> DevCodex : as long as the value after LIKE does not have a wildcard character at the beginning, then a regular index will be fine
<1> ahh ok
<1> and just for my edification,k
<1> if it does have a wildcard at the beginning, what are my options?
<1> like I said, right now we're only doing LIKE 'text%'
<0> hopefully mssql is smart enough
<2> options are (a) ****ty performance
<2> actually, just the one option
<1> hmm ok so no indexing will work if i have wildcard at the beginning then?
<1> in the case wehre we have 'text%', what kind of performance benefit will a regular non-clustered index provide?
<1> I guess what I'm asking is.. will the index create a perf gain?
<0> i am not sure
<0> but if mssql is smart enough then yes
<2> yes, it will
<2> however
<2> this is why people all over the world do something called TESTING!
<1> well of course ;)
<1> I plan to test right now.. just asking the guru for insight hehe
<1> so should i index table1.field and table2.field?
<1> or just table2.field?
<0> you are joining?
<1> yes there is joining involved in the query.. but the part i'm asking about is table1.field LIKE table2.field
<0> who the hell joins by a wildcard?
<3> lol.
<1> I'm not joining on a wildcard
<1> my LIKE is in the where clause
<0> but you are comparing it to another field in another table
<0> that's an inner join bro
<0> :(
<1> yep it sure is
<2> l8r - time to go shooting
<1> cya yuppie
<1> kexmex
<1> consider:
<1> table Authors (AuthorID int pk, Lastname varchar(50))
<1> table AuthorFilters (AuthorFilterID int pk, Filter varchar(50))
<1> AuthorFilters is a table of 'Rog%' type stuff
<1> let's say I have an Author with lastname 'Roger'
<1> how would you build the query to return all matching authorfilters?
<0> with a join
<1> didnt you just say that was stupid?
<0> yea
<1> ha
<1> uhh ok
<0> seriously...
<0> your filter table should be
<0> AuthorFilterID, AuthorID, Filter varchar(50)
<0> and you should join by AuthorID :)
<1> ha
<1> you're just cheating
<3> not at all
<3> he's optimizing the index
<1> it doesnt fit the requirement at all tho
<1> which is that the filter cannot be tied to one author
<0> what's the requirement
<0> how does that table get populated
<1> the filter wouldnt even be necessary if you had AuthorID in the AuthorFilters table
<1> the table gets populated by user input
<1> so something like
<1> I'll input 'Rog%' so I can see all authors with last name starting with Rog
<0> yea....
<0> that would look like this
<0> select * from auothers where lastname like @filter
<0> why is the filter in another table
<1> where should it be stored if not in a table?
<0> why do you store it though
<1> each user in the system has a set of filters
<0> explain more
<1> each user in the system says when I login, show me all authors that match my filters
<1> 'Rog%'
<1> 'Alb%'
<1> etc
<0> that match all his filters?
<1> and they want to login each day and see the new titles that match their filters
<1> right
<0> then you are right
<1> ok back to my question then
<1> should table1.field AND table2.field both have non-clustered indexes?
<0> i dont see the use of an index on filters
<0> since those records are going to be retreived by UserID i take it
<0> so UserID has to be indexed
<1> yep.. but a User could have thousands of filters
<0> yes but the table will be searched by UserID
<0> you might need to use Index HInts
<1> I see that searching by UserID helps narrow the search,
<1> but amongst the remaining results, you dont see an index helping?
<1> it sounds pretty intuitive to me
<0> look
<3> i've concluded you two are the same person.
<0> how's that?
<3> magic i guess? not sure. maybe some freaky friday ****
<1> brilliant
<0> i dont get what you are hinting at
<0> anyway
<0> dev, look
<0> say you have a list of names you need to find in a phonebook
<0> you would go one by one right?
<1> no dude
<4> is there a neat "one liner" to use return value from a stored procedure in a insert query?
<1> if i know the name i'm looking for starts with R,
<1> then i'm going to go right to the R section
<0> maggun: explain
<1> just like an index would facilitate
<0> dev
<0> dont interrupt
<0> look
<0> you have a list of names you need to look up in this huge directory of names
<3> ou'd start at page 1
<4> insert into table (column) VALUES (sp_procedure); <-- this is what I would like to do
<3> to chek where the letter X is at.
<0> so the list of names, you go one by one
<0> stfu
<0> maggu: i cant remeber exactly....try insert into table select from exec xp_x
<0> sp_whatever i mean
<0> ok so you have a list of names to look up in a huge directory which is indexed
<0> so you go one by one in your list and check them off as you find
<0> you dont need an index in your list of names you need to find
<0> you only need an index in the directory
<0> i am not sure how to explain it any better :)
<1> that's most certainly because you're not making sense :) the whole purpose of an index is to cut down on 'one-by-one's .. lets take the wildcards out of this example
<0> so you would only need to index the fliters column only if you are doing queries on particular indexes
<0> guy
<1> without wildcards,
<0> i am about to expload on you
<1> an index makes perfect sense
<0> are you stupid
<3> even with the wild cards
<0> i keep trying to explain to you
<3> it still uses the index
<0> **** man
<1> agreed ninjataku
<0> you need an index on the authors table on the author name
<0> because that's what you are search in
<0> you dont need an index on the ****ing filter column
<0> you said you need an index on both
<0> but you dont
<0> i keep trying to explain to you why
<1> no I asked if i did
<1> you coulda just said what you just did 10 minutes ago
<1> instead of the retarded phone book examples that dont hold
<1> thx
<1> later
Return to
#sql
or
Go to some related
logs:
#politics
#politics
irssi +piespy +imager
q308402
ICQ Pool Hack
#photoshop
as3 getBytesTotal()
#stocks
#stocks
#online
|
|