When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?
For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.
If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.
Any help, pointers, advice would be greatly appreciated, Gavin.
I am having problems w/ the indexes on a particular table. Currently the scan density if 25%. I have ran the dbcc dbreindex and the scan density does not improve. I have manually dropped and recreated all of the indexes on the table (there are only two) and still no help.
I have a clustered Primary Key index on my part_number and project_number fields and another index on the project_number field. If I drop the second index and only have my primary key index, it still doesn't help. The fill factor was originally set to 90% and I changed this to 30%, 20%, 10%, 0% and the best scan density I could acheive was 50%.
The data in this table is not updated that often, on average about 15 records a day are updated, and there are only 107 records. This is a small table, but there is a possibility for it to grow rapidly and I want to be prepared now.
Does anyone have any ideas or suggestions? I've tried everything I can think of with no luck.
I ran a DBCC Showcontig (tableid) and it tell me that one of my tables is only putting 0.19% data on each page. How can I control this for more data on a page. It tells me that I have 8078.8 bytes free per page.
A few days after droping and recreating noclustered indexes on a large table, scan density of most indexes falls below 20%. Only a few number of rows has deleted. Update operation didn't update these keys and there was a lot of inserts. Could anyone explain why are indexes fragmented so much?
I have attached a DBCC ShowContig for a table that has a less than 50% scan density value.
I vendor in insistent I have run their scripts incorrectly three times. I have run a "dbcc reindex" and even dropped primary key and indexes for a table and the scan density does not change. I have read white papers that state that on small tables you may never get a scan density of 100%. I am using this forum as a yardstick for this question.
------------------------------------------------------------------------------------------- ID Name Start Date End Date -------------------------------------------------------------------------------------------- 001 Project 1 2-2-2003 2-3-2007 002 Project 2 1-24-2003 2-6-2007 003 Project 3 4-10-2005 2-10-2008 004 Project 4 5-20-2006 6-6-2008 ... 015 Project 15 2-20-2006 3-3-2009 ---------------------------------------------------------------------------------------------- What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------ Year Projects No. Of Projects Starting Months ------------------------------------------------------------------------------------------------------------------------------------------------ 2003 Project 1 , Project 2 2 February, January 2005 Project 3 1 April 2006 Project 4, Project 15 2 May , Feb ------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...) and also the following
Output 2: (this is optional view...) ----------------------------------------------------------------------------------------- Year Start Date Project Name ----------------------------------------------------------------------------------------- 2003
February 2 Project 1 January 24 Project 2 2005 April 4 Project 3 2006 May 5 Project 4 February 20 Project 15 -------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT 2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end 3 FROM abc 4 Anyone can help? Thanks a lot.
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I am trying to pass several ids to use in a where clause. 1 For Each row as GridViewRow In gv_child.Rows 2 If row.RowType = DataControlRowType.DataRow Then 3 Dim chk as CheckBox = CType(row.FindControl("cb_Approve"),CheckBox) 4 If chk IsNot Nothing AndAlso chk.Checked Then 5 Dim id As Integer = CInt(CType(row.FindControl("lbl_id"),Label).Text) 6 ids &= "," & id 7 End If 8 End If 9 If ids.Length > 1 Then ids = ids.Substring(1) 10 'Submit to sql with ids as param... 11 Next
I am getting an exception. Error converting '38,39' to a column of datatype int. What am I doing wrong?
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name. At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
Hi everyone, I have a few classes representing my data layer for my ASP.Net 2.0 Web Application. For example, UserDAL, DocumentDAL, etc... All these classes derive from BaseDAL, which stores the SqlConnection in it. The DAL classes access it through a property. The property is defined like that: private SqlConnection connection;public SqlConnection Connection{get{ if (connection == null) connection = new SqlConnection(); if (connection.State != ConnectionState.Open) { connection.ConnectionString = ConnectionString; connection.Open(); } return connection;}} The constructor of each DAL class is overloaded and takes a SqlConnection object. So if I need to access two DAL classes, I pass the connection of the first, to the second to prevent opening and closing the connection again and again. Here is a sample: UserDAL userDAL = new UserDAL(); userDAL.DoSomethingAndAccessConnectionProperty();
// Now userDAL.Connection is opened. Pass the connection to the constructor of DocumentDAL DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection); documentDAL.AlsoDoSomething(); So, if I do the following:using (UserDAL userDAL = new UserDAL()) { userDAL.DoSomethingAndAccessConnectionProperty();
DocumentDAL documentDAL = new DokumentDAL(userDAL.Connection); documentDAL.AlsoDoSomething(); } I assume that the connection is removed from memory, even if the DocumentDAL class has also used it. Am I right? I want to be sure that there will be no open connections.Thanks
I have a sample photo database where we have added keywords to search for photos. I wanted a way to list all of the keywords that are in the database individually. The problem is in my keyword field there are many keywords seperated by a comma.
Ex: "bull, barrel, rodeo, western, cowboy" would in the keyword field for one photo.
I wanted to select distinct all of the individual words from each keyword field in all of the records.
hi guys i have a query that contains several table joins when i run the query without select distinct x,y,z,w,.. or order by docno it takes around 20 second to finish execution, when i add select distinct x,y,z,w,.. or order by docno it ruturns the same result in just 2 seconds is adding distict keyword or order by acts as an index for the query or what ?
. . . .
here is my query :
SELECT distinct p.indocno,p.CHAR_FIELD2_AR, p.CHAR_FIELD1, p.REVISION_NO, CAST(p.INDOCNO AS int) AS INDOCNO, p.CHAR_FIELD3, p.CHAR_FIELD7_AR, T.DESCRIPTION,J.DESCRIPTION AS [Section], p.SUBJECT FROM dbo.TECHNICAL_MAIN p INNER JOIN (SELECT MAX(revision_no) d, char_field1 c, char_field2_ar e, subcat_id j FROM technical_main m WHERE revision_no IN ('0', '1', '2') GROUP BY char_field1, char_field2_ar, subcat_id) b ON p.REVISION_NO = b.d AND p.CHAR_FIELD1 = b.c AND p.CHAR_FIELD2_AR = b.e AND p.REVISION_NO IN ('0', '1', '2') INNER JOIN dbo.CUST_HIERARCHY_LOOKUP T ON p.CHAR_FIELD7_AR = T.ID INNER JOIN dbo.CUST_HIERARCHY_LOOKUP J ON p.CHAR_FIELD3_AR = J.ID AND p.SUBCAT_ID = b.j
I have two datatables in my database. The first table, named Books, has two columns: BookID and Author (BookID is the primary key). The second table, named Purchases, has three columns: PurchaseID, BookID, BuyerID (Purchase ID is the primary key).
The idea here is that the Books datatable contains information regarding the book and its author while the Purchases datatable contains information on who has purchased what book.
Now, say I want to write an SQL query to extract a list of all the authors who have written a book purchased by buyer X. How would I go about doing this without having any duplicate entries? I figured that the following would work:
SELECT DISTINCT * FROM Books INNER JOIN Purchases ON Books.BookID=Purchases.BookID
But this ends up generating duplicate BookIDs if the Purchases table contains several buyers who have bought that Book. I know I could use BookID rather than * in the above query and that would work, but in reality I'm dealing with more complex tables and I would rather keep the * in there to actually get all the data out in one go.
I heard a claim recently at a SQL Server users group meeting that theTOP keyword forces sorting on a database server. I can't find anyreason this might be true and the Books Online say nothing about it.Can someone verify this claim one way or the other?
I am studying for the MSCE/MCDBA exam 70-229. In the book I am using("MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000Database Design and Implementation Exam 70-229, Second Edition") I amlooking at the section on the ANY/ALL keyword.<QUOTE>USE PubsSELECT TitleFROM TitlesWHERE Advance > ANY(SELECT AdvanceFROM Publishers INNER JOIN TitlesON Titles.Pub_id = Publishers.Pub_idAND Pub_name = 'Algodata Infosystems')This statement finds the titles that received an advance larger thanthe minimum advance amount paid by Algodata Infosystems (which, in thiscase, is $5,000). The WHERE clause in the outer SELECT statementcontains a subquery that uses a join to retrieve advance amounts forAlgodata Infosystems. The minimum advanceamount is then used to determine which titles to retrieve from theTitles table.</QUOTE>I don't understand why this references the "minimum advance". If yourun the subquery on its own, it returns the following values:5000.00005000.00005000.00007000.00008000.0000NULL[color=blue]>From my limited understanding, the "ANY" keyword applies to at least[/color]one value, but which one? How is this determined?Any help gratefully received.Edward--The reading group's reading group:http://www.bookgroup.org.uk
I am in the process of importing an Oracle database into SQL Server.Once of the tables has a field called "EXEC".SQL Server seems to reject any queries that include that particularfield because EXEC is a keyword. For eg.SELECT ID, EXEC from USERSresults in a syntax error near keyword EXEC.I can't change the fieldname becuase it will require reworking of awhole bunch of scripts.What can I do to adjust the query?Bijoy
Is possible in SQLSERVER to use a syntax like this?select x,y from table_1 where (x,y) not in (select h,k from table_2)I've tried, but it doen't work.Do you know any workaround?Thank youFede
I've been trying to figure out the usage of "Nothing" as the scope parameter, and the more I try, the more I can confused.
It says on the RDL spec:
"For expressions inside data regions:
.......
Specifying the keyword Nothing as the scope is equivalent to specifying the outermost data region containing the report item in which the aggregate is used."
It seems that what's it saying is that if I have an expression in a table as = last(somefield, "the name of the table") is the same as = last(somefield,Nothing) But, apparently it;s not. (I tried last, first,sum, count,CountRows, min, max...........) it doesn't matter where I put "=last(somefield,Nothing)" in the table (i tried table header,footer, detail, and table group header, footer), none of them worked. and I tried it everywhere in matrix, in charts. It's just not working. It always complains about the invalid scope not being the containing data region, containing grouping or dataset name.
However, the only way I get the "Nothing" as the scope to work is in a RunningValue function, not in any other aggregate funcitons.
Anyone help me with this, please... I need a complete definition on the usage of Nothing as scope.
where Description and Description2 are text datatypes.
I'm trying to return all records where myKeyword exists as a singular word in either of these two fields.
Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table?
I'm using an application that is generating some SQL scripts for SQL Server 2005. I'm trying tweak it so that I can run it on SQL Server 2000.
The line that I'm having trouble with is:
CREATE INDEX FKFFF41F9960601995 ON alf_access_control_entry (permission_id);(optional)
The key word "(optional)" is causing trouble. I understand this keyword, when used in SQL Server 2005, let's the script continue and complete when errors are detected.
What is the alternative syntax to use in SQL Server 2000?
