|
If you have to search every column of every table then you will just have to accept the poor performance as there is no magic way of making whst is basically a set of unindexed queries run fast.
You may be able to speed it up by generating sql for each table which excludes columns you know cannot be searched (identity columns for example would be meaningless), but maybe you should be looking at caching in a data access layer?
Hope this maybe helps.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: You may be able to speed it up by generating sql for each table which excludes columns you know cannot be searched (identity columns for example would be meaningless),
One way could be creating an indexed view on the varchar columns, but if I remember correctly Enterprise Edition is needed for that.
Ashfield wrote: maybe you should be looking at caching in a data access layer
That may be the only option if performance cannot be accepted and still everything needs to be searched...
|
|
|
|
|
shaik abdul gani wrote: we dont know what the user will type and want to search there is no constant keywords predefined
Then why don't you build the index for all words? When adding or updating the data in the database, check all varchar columns and separate all words from them. After that, add them to your index table(s) and apply references to the row containing the data.
If you want to do this in the db, I think it's just modelling question.
Another good approach is the one Bob provided if you can take this to application level.
|
|
|
|
|
thanx a lot for all of you for giving the views and suggestions . thanx a lot once again
|
|
|
|
|
You may take a look at Full Text Search, you could mix the strategy that the fellow developer has pointed before (scanning the tables names in the sys.objects table) plus querying the tables using FTS.
Google is your friend, you could find a lot of info about FTS.
Hope this helps.
|
|
|
|
|
good evening,
i can create a form ,i want to store data in database through form,but two textboxes values store in one column ,the controls names are
idproof(combobox),others(textbox).combobox have some values including "others",if we select any value except others, the textbox won't display but we can select others then textbox display and enter the value.my problem is,i want to the data in sqlserver database but i can use only one column to both controls,how can i store two controls datain one column but each time one control value is fired.
give me a solution to my problem. thank u for providing this site.
|
|
|
|
|
Lets see the code
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Vuyiswa Maseko wrote: Lets see the code
Don't be silly. If he had the code, he'd pass in his homework.
Jon
Smith & Wesson: The original point and click interface
|
|
|
|
|
Good Afternoon Guys
i have the Following Query
SELECT *
INTO #Rows
FROM OPENXML ( @doc , '/Timetable/Rows' , 2)
WITH ( ID int,
Descr varchar(64),
StartTime smalldatetime,
Duration int,
Offs varchar(64)
)
I understand the First Part
SELECT *
INTO #Rows
FROM OPENXML ( @doc , '/Timetable/Rows' , 2)
but i dont understand Why With ? can someone Explain what is With Doig with Columns
Thank you
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
the WITH is effectively giving you the table definition. SQL cannot work out datatype etc from xml, so you need to supply a mapping.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks man, it make sense
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Thats OK, you're welcome
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I had created a Stored Procedure.Its created successfully.I had written Cursor in that.
I had mentioned some of the code below:-
-----------
DECLARE InsertLog CURSOR local
FOR SELECT @ColumnName FROM [@TableName] WHERE @PrimaryColumn=@PrimaryValue
FOR READ ONLY
OPEN InsertLog
FETCH NEXT FROM InsertLog into @ColumnValue
----------
I am passing values like ColumnName TableName and where condition column and values as parameter
When I pass value to it gives an error
Invalid object name '@TableName'.
Can any body tell me why this error is coming.
It's urgent
|
|
|
|
|
I could be totally wrong here as I'm not a database expert but, @TableName is a parameter therefore it should not be enclosed in []. Try removing the [] from around @TableName.
|
|
|
|
|
Thanks for reply,
But if I try to remove it,it gives an error,
Must declare the table variable "@TableName".
|
|
|
|
|
Well, have you declared the @TableName parameter?
|
|
|
|
|
|
Show us the Whole Code
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
You cannot do a select from a variable unless the varaible is a table data type. The same applies to @ColumnName.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
This query is totally screwed.
You cannot do the following
ArifShaikh wrote: FROM [@TableName]
There is not such table as @TableName and SQL cannot evaluate the variable in the from clause.
You also cannot do the following
ArifShaikh wrote: WHERE @PrimaryColumn=@PrimaryValue
SQL does not evaluate the variable.
You are going to have to think up a completely different method for logging.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
My problem is that when i want to insert bulk data to my local sqlserver database it execute successfully.the code is:
insert into xmlproperty select * from openrowset(bulk 'C:\celtic\App_Data\PW_XMLfeed_15224.xml',single_clob) as XmlColumn
But when i insert same in remote database it generate error:
Msg 4834, Level 16, State 1, Line 1
You do not have permission to use the bulk load statement.
My question is can i insert bulk data to sqlserver like this way.if not, how can i insert?
wating for reply.
Thanks
snehasish
|
|
|
|
|
I think you need ADMINISTER BULK OPERATIONS server privilege.
|
|
|
|
|
i am using share hosting so sqlserver and xml file is in different places.can i insert this xml data to remote sql server using bulk copy?Does both database and file need to remain in same server?
|
|
|
|
|
snehasish wrote: can i insert this xml data to remote sql server using bulk copy
Yes you can but the SQL Server must have access to the file. Most likely you need to use domain credentials for the account under which the SQL Server is executed and make sure that this account has access to the file via network. Also use UNC-paths to specify the location.
snehasish wrote: Does both database and file need to remain in same server
As I described they don't have to, but it makes things a whole lot easier.
|
|
|
|
|
thanks.i will talk with hosting company according to your advice.
|
|
|
|