Improving SQL Server Searching - Buying A 3rd Component
Oct 26, 2007
Does any one know of any good components I can buy in to sit on top of my SQL Server to provide the kind of search functionality that google has - 'Did you mean...,' nearset match etc. I have set up SQL Server Full Text but to build all the extra functionality will take some time and I think it will be more cost effective to buy a component in. If any one has used any could you let me know of you experinces please.
Cheers
Scott
View 2 Replies
ADVERTISEMENT
Apr 3, 2007
Our SQL Developer asked for a new server with a separate small hard disk for the Transaction Log alone to reside on, to increase performance. This will be hard to do, since the servers we have been looking at are low-profile rackmount, and only hold 2 SATA disks. I hate to waste our only expansion bay on a small HD. Is this really something important, or will a Quad-core processor and plenty of RAM make the performance difference negligible? We have 32-bit SQL2000 licensed per processor, and our database is only about 26GB. I was hoping to get 1 large disk and partition it into a 20GB OS partition, and the rest would be for SQL. Am I totally on the wrong track?
My 2nd question is about RAM - if we get 4GB of RAM, will it decrease the performance if we get 64-bit O/S pre-installed instead of 32-bit? I know 64-bit O/S *can use* more RAM than 4GB, but does it *need* more RAM for the same level of performance that we have now? (We are planning to expand that to at least 8-12GB whenever we upgrade to 64-bit SQL2005, but the budget does not allow it just yet.)
Thanks!
View 3 Replies
View Related
Mar 18, 2008
Hi Everyone,
I am looking to buy SQL Server for my home computer solely for the purpose of training for Exam 70-431 Microsoft Certified Technology Specialist: SQL Server 2005. My inclination is to buy SQL Server Developer Edition from Amazon.com for $41.99. Is this the best thing for me to do? What is the difference between Developer, Express and Standard in terms of training for the certification exam?
Thanks!
View 6 Replies
View Related
Jun 2, 2007
Greetings...
I don't know if you have this question being asked before, but I searched and I didn't find. I'm not an expert in SQL issues.
I'm used to doing some programming on ASP on my personal computer. I use the SQL 2000 as a database - I used to use to SQL 2000 enterprise manager to manage my database on my local computer or to enter my database on the web hosting companies. Any way, my question is: I have now windows vista. I'd like to install SQL 2005 to do my usual work.
What version of sql 2005 should have for this personal use as mentioned above?
I totally appreciate any help or additional information regarding this topic since i'm not an expert and still new in this field
Thanks
View 3 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Feb 2, 2007
Hi.I am under the understanding that having the sql server 2005 db is notenough (like 2000 was) to debug stored procedures.. that I need topurchase (costly) visual studio 2005.Can someone suggest a free or lower cost alternative?sorry to be so cheap.. its the times I think.
View 4 Replies
View Related
Oct 24, 2005
Here's a sample SELECT statement:
SELECT T1.F3
FROM T1 INNER JOIN T2 ON T1.F4 = T2.F4
WHERE
(T1.F1 > @iNum AND T2.F1 > @iNum)
OR
( @iNum2 * (T1.F1 - T2.F1)/(T1.F2 - T2.F2) ) + (T1.F1 - ((T1.F1 - T2.F1)/(T1.F2 - T2.F2) * T1.F2) ) > @INum
As you can see, the second part of the WHERE (after the OR) is much more complicated than the part before the OR. My query would run a lot faster if it tried the first part of the OR and didn't bother with the second part if the first part was satisfied. Is there any way to do this?
Thanks!
Tyler
View 3 Replies
View Related
Feb 19, 2007
Hi,I used SQL Server 2005 on my development machine, and whilst thismachine isn't as powerful as the live server, it does at times seem alittle slower than I would expect. So I've been wondering if there isany way for me to tune the machine so that SQL Server is better able tomake use of the resources? I am using WS2003.Short of tweaking with the actual database, which is still underdevelopment, does anyone have any tips to increase peformance?Thanks,--Dylan Parryhttp://electricfreedom.org | http://webpageworkshop.co.ukProgramming, n: A pastime similar to banging one's headagainst a wall, but with fewer opportunities for reward.
View 16 Replies
View Related
Sep 23, 2007
I've written a small application that uses a Microsoft Sql Server 2000 Database and Here lately i've been experiencing alot of performance problems with timeouts and data just taking too long to retreive. I was wondering what could i do to help with this.
Details:
My Program i wrote simply takes windows eventlogs and uploads them into a central database in general , i have also build a small asp.net page, where users could write out queries or save them for later use. The program is hitting our servers and it grabs them remotely, inserts into sql one by one, and clears out the logs when finished, it runs one a day, and generates about 12,000 events daily. (mostly login's and logoffs). once it starts it'll take about 2 and a half hours to finish going through everything.
Database Design...
Single Table, about 3.5 million rows now, takes up about 3 gigs of harddrive space.
Column Definitions
Name, Data Type
EventEntryID INT PrimaryKey(Clustered) (Auto-Increment Identity)
Log varchar(300)
Type varchar(300)
Date smalldatetime
Source varchar(300)
Category varchar(300)
Message varchar(8000)
EventID varchar(300)
UserName varchar(300)
Computer varchar(300)I know the database design can probally use some work, but it's hard to do so now b/c of the massive amoutns of data without killing the transaction log.
Computer Specs:
OS: Window Server 2003
Processor: Intel 700 MHz
RAM: 1 GB
I know it's a small server, but this is pretty much the largest thing this server deals with
I've tried to monitor sql with perfmon and sql performance counters and I just don't see much of anything going wrong with it there, so i'm thinking the the hardware isn't an issue, but then again, i'm not very familar with sql.
View 10 Replies
View Related
Mar 28, 2006
hello everyone,
does anyone know some tipps or articles that descripe how the sqlserver 2000 can be improved? i have an application which displays newspapers. the text of the newspapers is saved in db. performance is ok but i have performance-problems when i do a fulltext-search.
1) hardware: p4 xeon double core, 2.4 Ghz, 2 GB Ram
2) number of articles in database: ~ 255.000 Records (betwenn 10 - 400 words each)
3) the database is indexed (i am searching with "CONTAINS" and not with the "LIKE"-keyword)
4) in my query i am searching in 3 columns (title,leadtext,content)
5) results (searching for one word, stopping after 150 Results):the time to get a result from database lies between 5 to 10 Seconds (Average: 6.77 sec)!
this is the time only for getting the dataset from the sqlserver. the time of manipulating the dataset and binding it to my repeater needs about 15 milliseconds. using a sqldatareader didn't improve the performance. i got the same results.
in future the number of records will be three to five times higher than now. i am afraid that then the user has to wait half a minute to get a result of 150 search hits.
my questions:
Are you having similiar results in your applications? Are this times acceptable?
The SqlServer 2000 (professional) is installed in its default way with the default settings. Are they possiblities to modify this settings for improving performance? Where can i find info about that?
greetings and thx
Klaus
View 2 Replies
View Related
May 16, 2007
Hi all,
My colleague and I are having some difficulties regarding the speed of a LIKE query on our intranet system. Please see the quote below as posted on another SQL forum (To which no one has responded yet ).
Quote: Howdy,
I've taken control of an SQL Database with an ASP front end. The CPU usage of the SQL Server process periodically jumps up to between 75-99%. I've managed to identify the ASP page that is causing it and it's a search page.
It's basically doing something like this:
SELECT JobNumber, CustomerName, CustomerAddress, CustomerPostCode
FROM Customers
WHERE CustomerName LIKE '%query%'
OR CustomerAddress LIKE '%query%'
OR CustomerPostCode LIKE '%query%'
Where query is the value the user has searched on.
The CustomerName and CustomerPostCode fields are varchar fields, but the CustomerAddress field is a text field.
I know if I replace the CustomerAddress text field with a series of varchar fields then I could make them non-clustered indexes, but would this help speed things up with a LIKE query?
There's a lot of work required on the ASP side if I'm going to split the field out, so I only want to do it if I'm gonna get a significant benefit from doing it.
Failing this, is there a better way of performing a search like this?
Any help is much appreciated,
Thanks!
View 9 Replies
View Related
Feb 24, 2008
Hi guys,
I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.
select top 100 * from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 200 ms to 600 ms to execute.
select count(*) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 2000ms to 4000 ms to run.
The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.
Are there any other things that i can do to improve the counting of total records?
Thanks.
View 5 Replies
View Related
Nov 4, 2006
All:
I am presently tasked with improving the performance of a chatty application. What I mean by a chatty application is that the application makes multiple calls to the database server for each user request. In many cases it appears that many of the windows are making multiple calls to the database for the population of dialog boxes. In a couple of cases there are more than 10 dialog boxes that are populated similar to this.
To me this is kind of an "old issue", but my response is (1) cache the dialog information as much as possible and (2) make one call to the database to return all of this data for the dialog boxes if it is not cached. For update calls again make a single call to the database rather than making a call for each individual row of a table that is updated.
I admit that some of this will complicate the code of the application; however, my perspective is that I am supposed to look at this from the database perspective and not from the perspective of "programmer convenience." There will be times in which something that would otherwise get to hairy for a programmer will dictate an additional trip to the server, but this should not normally be the case.
What else do I need to consider?
Dave
View 3 Replies
View Related
Sep 20, 2006
I have a varchar(max) field that is on its way to a Term Lookup task, so it requires conversion to DT_NTEXT before it gets there. The conversion is taking a really, really, really long time. In my current example I have about 400 rows. Granted the length of the varchar(max) if I 'select max(len(myColumn))' is approximately 14,000,000 so I understand that I'm moving a lot of data. But I'm on a x64 machine with 4 dual core processors and watching Task Manager I can see that I'm only using 1 core out of 8. Anything I'm missing that can speed this up?
Thanks,
Frank
View 3 Replies
View Related
Dec 5, 2007
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block
SET STATISTICS IO ON
Declare @CustomerID int
SET @CustomerID = 1
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
Cheers,
Justin
View 3 Replies
View Related
Aug 15, 2007
We have a table that is 800GB. We are planning to re-build the clustered index on this table to a different filegroup. The new filegroup and files associated with it will sit on a SAN which will have a 1.5TB allocation. Does anyone have any suggestions in regards to how many files to have associated with the filegroup to provide optimal performance? Apparently we could have 3 LUNS (500gb each), so would 1 file on each LUN provide additional performance as opposed to one file on 1 LUN?
View 1 Replies
View Related
Feb 4, 2008
Is there a configuration or a trick to improve the speed of the access to inserted and deleted tables whithin a trigger? Whenever a trigger is called, the access to inserted or deleted constitute approximatly 95% of the execution time.
Is there a way to have access to inserted and to deleted improved other than copying the data to another table?
View 3 Replies
View Related
Dec 6, 2007
Hi
Iam working on a portal where there is a search option. Here, we wanted to search four tables where each table is having three columns as below:
Table1(col1 ntext,col2 varchar,col3 ntext)
Table2(col1 ntext,col2 varchar,col3 ntext)
Table3(col1 ntext,col2 varchar,col3 ntext)
Table4(col1 ntext,col2 varchar,col3 ntext)
The problem here is as how do i search the above four tables on the respective three columns each of each table. Then I wanted to give out the result in a table format with respective data values in the columns EX:
Output table(S.No,Ti,Coli Ti.coli)
The rest i can do it to pull this information from UI and display it. I got stucked up here of searching four tables effectively and with performance ease. I am hoping that somebody would have already faced a similar search issue for their portals and would have used some code.
Any pointers on this is highly appreciated.
Thanks!
View 2 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Mar 31, 2008
hi there,i have a textbox in my page and a button,when the button is clicked the application will search for the text written in the textbox in my database which is sql server, it works fine in my system but when i upload my website in the web it doesn't work correctly i mean that it dosent find all the matches, why this happens? is it possible that this problem occur because of the different fonts which is used in sql server and the font is used in my application?
thanks for help
View 3 Replies
View Related
Jun 22, 2005
Hi,the Soundex search words that sounds similar.Does MS SQL Server has some function to make some intuitive search?For example, for search term database, it should return rows that contains: "database" word, but also rows that contains "Oracle", "MySQL", "MS SQL" etc. terms.
View 1 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Jun 11, 2008
Hi ,
I'm using sql server 2005. In my 'Books' table some of the 'subject' column contains data's with single and double quote. example: Quantu"m phys'ics
i'm passing the subject as parameter and it have to display results if the corresponding subject name is in the table. Here is my code... i'm using dynamic query...
alter procedure getbooks
@sub varchar(200)
as
begin
declare @sqlq nvarchar(2000)
set @sqlq='select subject from books where subjectname like '''+@sub+'%'''
exec sp_executesql @sqlq
end
How can i search for words like this Quantu"m phys'ics in the above query? Any one who knows how to do this please send me the code..
View 9 Replies
View Related
Nov 3, 2004
I was wondering how exactly one can search the Active Directory with SQL Server 2000. There is all kind of stuff on the Internet about how to connect them and how useful it is to connect them together - plus how you can use Active Directory to query SQL Server, but I`ve never encountered any article where they explain how SQL Server can search the Active Directory.
I`m thinking about how one has their user-database in Active Directory with all users in groups, but only uses groups in SQL Server. How exactly can SQL Server figure out how a user belongs to a group?
Does Active Directory tell SQL Server about that when the user tries to access something from SQL Server, or does SQL Server already knows which group is accessing it as soon as the user authenticates in Active Directory?
View 7 Replies
View Related
Feb 17, 2014
I have french word like "Services d'organisation de minaires"..and i want to search word in SQL SERVER 2008. but the main problem is SQL SERVER give an Error because of string break.Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'organisation'.
View 1 Replies
View Related
Mar 5, 2015
All I really need to know is how to see if a specific node exists. Here's the XML, as well as the query I have:
<PaymentData xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="Com.Commerce.Data/PaymentData.xsd"><CreditCardResult Code="0" Message="APPROVED" Last4="xxxx" AuthorizationNumber="123456" Amount="6.34000000" /></PaymentData>
Basically I just need to see if the CreditCardResult node exists, and return that relevant row. That's it!
Here's the where clause so far. I've tried every variation I can think of and checked tons of forums and tech sites.. no luck..
SELECT TOP 1000 * FROM tblOrder
WHERE zPaymentData.exist('(/PaymentData/CreditCardResult[1])') = 1
View 4 Replies
View Related
Jul 26, 2007
Hi
We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?
The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?
Please advise, thanks for your help!
Li
View 4 Replies
View Related
Apr 8, 2015
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C
On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%'
Where
(
Contains(A.[Description], @KeyWordSearch)
And A.OpenTime Between @StartDate and @EndDate
And C.Classification = @GroupNameClassification
)
The code is throwing:
Msg 7630, Level 15, State 3, Line 46
Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
View 2 Replies
View Related
Oct 13, 2006
Превед!Prompt me please some asp.net 1.1 pivote table component that can use both SQL Server database (SQL queries) and Analyse Services database (MDX queries).Thanks.
View 3 Replies
View Related
Jun 28, 2001
hi geeks,
I need to build a generic text search component for MS SQL server 7.0. It is basically one of those Database searches that appear on web sites. Is there any stored procedure or service that MS SQL server provides for text based search on a database. Mind u my requirement is not to search a table but aan entire database....
anyone done this before.....
keep the faith
Deepesh
View 1 Replies
View Related
