Hi everyone,
My company has a website, use ms sql server. One table has more than 1000000 records.
When users search data from this table(such as search records which contain the word "school"
in NewsTile field.
And the server often occurred deadlock error.
How can I improve it?
Thanks.
P.S. The table has these fields:
NewsID
NewsTitle
NewsContent
NewsClickTimes
NewsInsertTime
Hello,I am looking at optimizing site searching on a web application. I have two thoughts on the idea:1. create views with fulltext indexes combining records from multiple tables.2. create a table with an xml column and primary index. I understand the xml column type has the overhead of a BLOB under the hood, but that a primary xml index can "shred" the contents and improve parsing. I also read the xml column is actually searched as a tree, providing some variant of log(n) run time. Does anyone know of good literate on this subject, the more big O notation, runtime analysis types of posts the better.Thanks
View 5 Replies View RelatedHi all,
I have a sql script that updates records in a table with 40 million records.
There is some functionality in the script that could be put away in functions for code reuse/elegance.
Functions would cause execution overhead.
What else could I use besides functions that would allow me the code reuse and not compromise the execution over head? Is there any thing like includes in TSQL that would allow me to do so?
TIA..
I have a function which is writing thousands of records coming from Access database and I was wondering if someone can suggest how can reconstruct my code for faster processing. Here my code: Public Sub MigrateNFSData(ByVal calcTbl As DataTable, ByVal strDBConnection As String) Dim sqlServerConn As New SqlConnection(strDBConnection) 'Define stored procedures Dim command As New SqlCommand Dim getAccID As New SqlCommand("GetAccountID", sqlServerConn) Dim getActionID As New SqlCommand("GetActionID", sqlServerConn) Dim getExchangeID As New SqlCommand("GetExchangeID", sqlServerConn) 'Dim getParrentAccID As New SqlCommand("GetParentAccID", sqlServerConn) Dim getStatusID As New SqlCommand("GetStatusID", sqlServerConn) Dim getTraderID As New SqlCommand("GetTraderID", sqlServerConn) Dim getGroupID As New SqlCommand("GetGroupID", sqlServerConn) Dim getGroupIDByIP As New SqlCommand("GetGroupIDByIP", sqlServerConn) Dim getTraderIDByIP As New SqlCommand("GetTraderIDByIP", sqlServerConn) 'Define insert records stored procedures Dim insertAcc As New SqlCommand("InsertAccount", sqlServerConn) insertAcc.CommandType = CommandType.StoredProcedure Dim insertAction As New SqlCommand("InsertAction", sqlServerConn) insertAction.CommandType = CommandType.StoredProcedure Dim insertExchange As New SqlCommand("InsertExchange", sqlServerConn) insertExchange.CommandType = CommandType.StoredProcedure Dim insertGroup As New SqlCommand("InsertGroup", sqlServerConn) insertGroup.CommandType = CommandType.StoredProcedure Dim insertStatus As New SqlCommand("InsertStatus", sqlServerConn) insertStatus.CommandType = CommandType.StoredProcedure Dim insertTrader As New SqlCommand("InsertTrader", sqlServerConn) insertTrader.CommandType = CommandType.StoredProcedure Try sqlServerConn.Open() Catch ex As Exception MessageBox.Show("Connection failed to open!") End Try 'Set parameters to helper Get Stored Procedures to retreive Id's getAccID.Parameters.Add("@AccName", SqlDbType.NVarChar) getAccID.CommandType = CommandType.StoredProcedure getActionID.Parameters.Add("@ActionName", SqlDbType.NVarChar) getActionID.CommandType = CommandType.StoredProcedure getExchangeID.Parameters.Add("@ExchName", SqlDbType.NVarChar) getExchangeID.CommandType = CommandType.StoredProcedure 'getParrentAccID.Parameters.Add("@ParentName", SqlDbType.NVarChar) 'getParrentAccID.CommandType = CommandType.StoredProcedure getStatusID.Parameters.Add("@StatusName", SqlDbType.NVarChar) getStatusID.CommandType = CommandType.StoredProcedure getTraderID.Parameters.Add("@TraderName", SqlDbType.NVarChar) getTraderID.CommandType = CommandType.StoredProcedure getGroupID.Parameters.Add("@GroupName", SqlDbType.NVarChar) getGroupID.CommandType = CommandType.StoredProcedure getGroupIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getGroupIDByIP.CommandType = CommandType.StoredProcedure getTraderIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getTraderIDByIP.CommandType = CommandType.StoredProcedure command = New SqlCommand("InsertTradeTransaction", sqlServerConn) command.CommandType = CommandType.StoredProcedure 'Set Parameters for Insert stored procedures insertAcc.Parameters.Add("@Account", SqlDbType.Text) insertAction.Parameters.Add("@ActionName", SqlDbType.Text) insertExchange.Parameters.Add("@Exchange", SqlDbType.Text) insertGroup.Parameters.Add("@Group", SqlDbType.Text) insertGroup.Parameters.Add("@ACCID", SqlDbType.Int) insertGroup.Parameters.Add("@GroupID", SqlDbType.UniqueIdentifier) insertStatus.Parameters.Add("@StatusName", SqlDbType.Text) insertTrader.Parameters.Add("@Group", SqlDbType.UniqueIdentifier) insertTrader.Parameters.Add("@IP", SqlDbType.Text) insertTrader.Parameters.Add("@TraderName", SqlDbType.Text) insertTrader.Parameters.Add("@TraderID", SqlDbType.UniqueIdentifier) 'Adding stored Get Stored Procedure's parameters----------------------- command.Parameters.Add("@OrderNum", SqlDbType.Text) command.Parameters.Add("@ACC_ID", SqlDbType.Int) command.Parameters.Add("@Group_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Trader_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Exch_ID", SqlDbType.Int) command.Parameters.Add("@Date", SqlDbType.DateTime) command.Parameters.Add("@Time", SqlDbType.DateTime) command.Parameters.Add("@ActionID", SqlDbType.Int) command.Parameters.Add("@StatusID", SqlDbType.Int) command.Parameters.Add("@TimeSent", SqlDbType.DateTime) command.Parameters.Add("@Qty", SqlDbType.Int) command.Parameters.Add("@Product", SqlDbType.Text) command.Parameters.Add("@MMYYY", SqlDbType.Text) command.Parameters.Add("@ExchOrderID", SqlDbType.Text) command.Parameters.Add("@TimeTicks", SqlDbType.Int) command.Parameters.Add("@W2G", SqlDbType.Int) command.Parameters.Add("@W2Exch", SqlDbType.Int) command.Parameters.Add("@G2ExchDerived", SqlDbType.Int) command.Parameters.Add("@Msg", SqlDbType.NVarChar) 'command.Parameters.Add("@ExchDate", SqlDbType.DateTime) 'command.Parameters.Add("@ParentID", SqlDbType.Int) 'Paremeters Defenition-------------------------------------- 'Parsing DateTime Objects Dim formaterA As IFormatProvider formaterA = New System.Globalization.CultureInfo("en-GB", True) Dim dateObj As Date 'DEBUG 'Dim rows = calcTbl.Rows.Count Dim colValues = GetColumnsValues(calcTbl) 'Write table with computed NFS data to sql server DB For Each dr As DataRow In calcTbl.Rows Dim orderNo = dr.Item("Order No").ToString() command.Parameters("@OrderNum").Value = dr.Item("Order No").ToString() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() If getAccID.ExecuteScalar() = Nothing Then insertAcc.Parameters("@Account").Value = dr.Item("Acct").ToString() insertAcc.ExecuteNonQuery() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() command.Parameters("@ACC_ID").Value = getAccID.ExecuteScalar() Else command.Parameters("@ACC_ID").Value = Int32.Parse(getAccID.ExecuteScalar()).ToString() End If getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If getTraderID.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() If getTraderID.ExecuteScalar() = Nothing Then getTraderIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getTraderIDByIP.ExecuteScalar() = Nothing Then insertTrader.Parameters("@Group").Value = getGroupID.ExecuteScalar() insertTrader.Parameters("@IP").Value = dr.Item("IP").ToString() insertTrader.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() insertTrader.Parameters("@TraderID").Value = Guid.NewGuid insertTrader.ExecuteNonQuery() command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() Else command.Parameters("@Trader_ID").Value = getTraderIDByIP.ExecuteScalar() End If Else command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() End If getExchangeID.Parameters("@ExchName").Value = dr.Item("Exch").ToString() If getExchangeID.ExecuteScalar() = Nothing Then insertExchange.Parameters("@Exchange").Value = dr.Item("Exch").ToString() insertExchange.ExecuteNonQuery() command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() Else command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() End If getActionID.Parameters("@ActionName").Value = dr.Item("Action").ToString() If getActionID.ExecuteScalar() = Nothing Then insertAction.Parameters("@ActionName").Value = dr.Item("Action").ToString() command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() Else command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() End If getStatusID.Parameters("@StatusName").Value = dr.Item("Status").ToString() If getStatusID.ExecuteScalar() = Nothing Then insertStatus.Parameters("@StatusName").Value = dr.Item("Status").ToString() insertStatus.ExecuteNonQuery() command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() Else command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() End If 'getParrentAccID.Parameters("@ParentName").Value = "" 'If getParrentAccID.ExecuteScalar() = 0 Then 'insert parent acc 'Else 'command.Parameters("@ParentID").Value = getParrentAccID.ExecuteScalar() dateObj = Date.Parse(dr.Item("Exch Date").ToString(), formaterA) command.Parameters("@Date").Value = dateObj command.Parameters("@Time").Value = DateTime.Parse(dr.Item("Time").ToString()) command.Parameters("@TimeSent").Value = DateTime.Parse(dr.Item("Time Sent").ToString()) If (dr.Item("Qty").Equals(System.DBNull.Value)) Then command.Parameters("@Qty").Value = System.DBNull.Value Else command.Parameters("@Qty").Value = Int32.Parse(dr.Item("Qty").ToString()) End If command.Parameters("@Product").Value = dr.Item("Product").ToString() command.Parameters("@MMYYY").Value = dr.Item("MMMYY").ToString() command.Parameters("@ExchOrderID").Value = dr.Item("Exchange Order ID").ToString() If (dr.Item("Time Ticks").Equals(System.DBNull.Value)) Then command.Parameters("@TimeTicks").Value = System.DBNull.Value Else command.Parameters("@TimeTicks").Value = Int32.Parse(dr.Item("Time Ticks").ToString()) End If 'command.Parameters("@ExchDate").Value = Date.Parse(dr.Item("Exch Date").ToString()) 'command.Parameters("@ExchDate").Value = Convert.ToDateTime(dr.Item("Exch Date").ToString()) 'DEBUG 'Dim strW2G = dr.Item("W2G").ToString() 'Dim strW2E = dr.Item("W2E").ToString() If (dr.Item("W2G").Equals(System.DBNull.Value)) Then command.Parameters("@W2G").Value = System.DBNull.Value Else command.Parameters("@W2G").Value = Int32.Parse(dr.Item("W2G").ToString()) End If If dr.Item("W2E").Equals(System.DBNull.Value) Then command.Parameters("@W2Exch").Value = System.DBNull.Value Else command.Parameters("@W2Exch").Value = Int32.Parse(dr.Item("W2E").ToString()) End If 'command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) If (dr.Item("Time Delta G2E").Equals(System.DBNull.Value)) Then command.Parameters("@G2ExchDerived").Value = System.DBNull.Value Else command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) End If command.Parameters("@Msg").Value = dr.Item("Msg").ToString() command.ExecuteNonQuery() Next sqlServerConn.Close() End Sub
View 3 Replies View Related
Hi,
I have to search the records after the records populated.
I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.
For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.
Please tell me how to implement in this report,
Thanks and Regards
Altaf Nizamuddin
Hi,I am using SQL 2000 and has a table that contains more than 2 millionrows of data (and growing). Right now, I have encountered 2 problems:1) Sometimes, when I try to query against this table, I would get sqlcommand time out. Hence, I did more testing with Query Analyser and tofind out that the same queries would not always take about the sametime to be executed. Could anyone please tell me what would affect thespeed of the query and what is the most important thing among all thefactors? (I could think of the opened connections, server'sCPU/Memory...)2) I am not sure if 2 million rows is considered a lot or not, however,it start to take 5~10 seconds for me to finish some simple queries. Iam wondering what is the best practices to handle this amount of datawhile having a decent performance?Thank you,Charlie Chang[Charlies224@hotmail.com]
View 5 Replies View Related
I have worked with many data mining softwares and found SQL Data mining part of Microsoft product needs to imrove a lot to whoo the customers.
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies View RelatedNow Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!
in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:
1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very
confusional , and very difficult to maintance.
2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.
You should not give limitation to Sql Function, should just limit the using context of Sql Function!
The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!
Hi !!
We are doing a project where we have a table with 800,000 records. We need to implement a search on this 800,000 records. How do we do that?
Assue State = NJ
Criteria: Select County, Select City, Enter Zip Code, First Name and Last Name
This should bring up Information about the person.
How can we implement it efficiently ?
Thanks
Hello I am trying to create a stored procedure where the users can either type in the last name, or a wild card in order to get the values they are looking for. Sometimes they are not sure how to spell the last name and they will type in the first 2 letters or an * for all to see if they can find it that way. How can I do this??
Here is what I have so far
Code Block
ALTER PROCEDURE [dbo].[AdvSearchRevocations]
(@Enter_LastName nvarchar(25))
AS
SET @Enter_LastName = REPLACE(@Enter_Lastname, '*', '%')
SELECT LastName, FirstName, ReasonOfRevocation, TM#, [I/R #], Date
FROM dbo.Revocations_Tbl
I have to create a search textbox where if i key in the word "dog",it will search against a field called "Name" in a table.Then it will return all records where the text/data in the "Name" field contains the word "dog".For example,it will return the records where the text/data in the "Name" field has words such as "hotdog","doggie","dog barking","big dog" etc etc.
Can i use simple SQL for this or do i need to configure the full text search service on SQL server 2000 and use the FREETEXT predicate?Thank you in advance for any replies.
Just like Unique/Distinct command, is these some way I could list just the duplicate records from a table . The field is numeric.
Thanks a lot for you help.
I'm just learning to join tables and I'm trying to construct a query to tell me the following:
Table names and related columns:
Table1.HdrSys
Table2.HdrSysNum and HdrSys
Table3.HdrSysNum
Table3 contains the detail info for Table2. Table1 is the header table for Table2. So.. If I want all records in Table3 that don't have a related record in Table1, is this even possible?
I started with:
select * from Table3 a
join Table2 b on a.HdrSysNum = b.HdrSysNum
join Table1 c on b.HdrSys= c.HdrSys
-- Now.. how do I qualify the statement.. or can I with just this. Thx! Hope I wasn't too confusing.. because I tend to get that way when I'm confused!
Hey folks...So I have a table that looks like this:CREATE TABLE [tblStation] ([CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),[PHONE] [varchar] (10),[EVENTTIME] [datetime] ,[STATION] [int],[OPERATOR] [varchar] (16),[EVENTCODE] [varchar],[CALLSPAN] [decimal](18, 0),[FDISP] [int],[RECORDNUM] [varchar],[STC] [varchar],[PROMOC] [varchar],[EXP_CAMP] [varchar],[PROMO3] [varchar],[MAXATT] [char],[LISTNAME] [varchar],[SITENAME] [char],[Row_id] [int] IDENTITYIt's taking nine seconds to run the following command:SELECT count([fdisp])FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)WHERE fdisp IS NULLAnyone familiar with a table of this size having performance likethis? The [fdisp] column has a non clustered index on it.Thanks in advance...
View 1 Replies View Relatedhaving some issues trying to create a query in excel 2013. I can get the data I want from sql, but I get individual transactions and I want to sum them by plu number. here is my query, I tried using group by but every time I add the field, I get an error that some other field is invalid because it's not contained in an aggregate or group by clause. btw, I didn't name these fields.
SELECT RPT_ITM_D.F254 as [Date], RPT_ITM_D.F01 As [PLU], RPT_ITM_D.F64 As [Qty Sold], RPT_ITM_D.F65 As [SOLD], OBJ_TAB.F17 As [RCode], OBJ_TAB.F29 As [Description], PRICE_TAB.F30 As [EL Price], PRICE_TAB.F31 As [Qty]
FROM STORESQL.dbo.OBJ_TAB OBJ_TAB, STORESQL.dbo.PRICE_TAB PRICE_TAB, STORESQL.dbo.RPT_ITM_D RPT_ITM_D
WHERE OBJ_TAB.F01 = RPT_ITM_D.F01 AND OBJ_TAB.F01 = PRICE_TAB.F01 AND PRICE_TAB.F01 = RPT_ITM_D.F01 AND ((RPT_ITM_D.F254>=? And RPT_ITM_D.F254<=? )AND (OBJ_TAB.F17=25))
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
View 6 Replies View RelatedHi,
I have a table full of items which can be searched.
I also have another table with the ID of each item and columns for no of times details shown, no of times saved etc.
What I would like to do is increment a value in this second table for each item every time it is returned in a search.
What would be the best way to do this?
(Im using a Stored Procedure)
Thanks in advance,
Pete
I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B
To illustrate:
Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we
[code]....
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
View 4 Replies View RelatedHello all,
I am making a query which will select those records from table where a column data ends with a particular extension,i.e.
I want the query to fetch the rocords where FileName(column name) ends with .JPEG or .GIF or .BMP.
I have five records in table and FileName(columns name) contains three .JPEG ,one .GIF & one .BMP files name.
How can i get for a particular extension?
Thanks in advance
I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?
I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?
Hi
I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.
* Company Table (around 20 fields):
companyID
companyName
categoryID
state
postCode
etc.
* Category Table (5 fields)
categoryID
categoryName
etc.
We are using MSSQL 2005 Express Edition with Advanced Services
A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).
1) What kind of response times should I expect for the free text search (I have not used the free text search before)
2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?
Any suggestions appreciated.
Best Regards
Kevan
I have one table with 300,000 records and 30 columns.For example columns are ID, COMPANY, PhONE, NOTES ...ID - nvarchar lenth-9COMPANY - nvarchar lenth-30NOTES - nvarchar length-250Select * from databasewhere NOTES like '%something%'Is there a way to get results from this query in less then 1-2 secondand how?
View 7 Replies View RelatedI'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.
How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)
First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)
First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)
HI,
I have been trying to solve the locking problem from past couple of days. Please help mee!!
Scenario:
--------------
I have a SSIS package in which 2 data flow tasks. 1st data flow task deletes records from a 5 tables and the 2nd data flow task should insert records into 1 of the five tables after the success of 1st data flow task. This scenario runs in Transacation.
The above scenrio in the 2nd data flow task hangs in runtime. It does not complete. with sp_who2 command i could see that there is an intent share lock(LK_M_IS) on the table and the status is SUSPENDED.
I dont know how to come out of this locking. Please help.
Thanks ,
Sunil
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
I want to select one field from a table,but it should on some conditionswhich refer to 5 table ,such as A.FILED1=B.FIELD1 AND B.FIELD2=C.FIELD3 AND....Should I use case "select sum(a.amount) from a,b,c,... wherea.field1=b.field1 and b.field2=c.field2 and ..." or "select sum(a.amount)from select b.field1 from select c.field2 from...."?And which case is moreefficiency?thanks!我想计算一个表中的某个字段的和，但此记录需在从多个 表中查询此记录是否满足特定的条件。那么我是用select ..from ...where ..and ..and..and ..and ..还是用select ..fromselect ..from select ..from ......？请问是哪一个效率高？谢谢！
View 2 Replies View RelatedHi guys,
Since the project that i'm developing is rapidly increasing, the pages seems to be getting slower everytime you view it. I would like to ask if code below would be efficient enough for several simultaneous request of data or if you have any other suggestions, you are welcome to add:
1 Public Shared Function QueryDatabase(ByVal sql As String) As DataTable2 3 ' SQL Server Connection Object Variable4 Dim _oConnection As SqlConnection5 ' SQL Server Command Object Variable6 Dim _oCommand As SqlCommand7 ' SQL Server Data Adapter Object Variable8 Dim _oAdapter As SqlDataAdapter9 ' DataTable Object Variable (Early Binding)10 Dim _oDataTable As New DataTable11 12 ' Instantiate Connection Object with connection string13 _oConnection = New SqlConnection("Data Source=XXX.XXX.XXX.XXX;Initial Catalog=XXXXXX;User=XXX;Pwd=XXX;")14 ' Instantiate Command Object with SQL String and Connection Object15 _oCommand = New SqlCommand(sql, _oConnection)16 ' Instantiate Data Adapter Object with Command Object17 _oAdapter = New SqlDataAdapter(_oCommand)18 ' Fill the DataTable Object with the retrieve records19 _oAdapter.Fill(_oDataTable)20 21 ' Release resources used by DataAdapter Object22 _oAdapter.Dispose()23 24 ' Release resources used by Command Object25 _oCommand.Dispose()26 27 ' Close the connection of the Connection Object from SQL Server28 _oConnection.Close()29 30 ' Release resources used by Connection Object31 _oConnection.Dispose()32 33 ' Return the retrieve records34 Return _oDataTable35 36 End Function Thanks a lot.
Hey,I am developing a website which will be used by a large number of people so I am concerned about efficiency.Sorry for the three posts but anyone with any info would be appreciated.The database has the following tables: FACILITY-----MEETING ---- | | USERS---- -------- MEETING_INVITE -------- REMINDER | | ---------CONTACTS-------When the user logs in I use there username to access the rest of the tables. I get all of the users information out of the database in one go and store it in a dataset.So when a user accesses there meetings page, I pass the dataset to that page with a server transfer.Question 1 > Is it more efficient to open the database once and access all the information and pass the information to seperate tables or is it more efficient to access the database on the individual pages and thus not passing of information.---------------------------------------------------------------------------------------------------------------In order to access the information I use 6 Select statements in a rowHere is an example of my select statments: SELECT * FROM USERS WHERE email = textbox_emailSELECT FACILITY.* FROM FACILITY, USERS WHERE FACILITY.email = USERS.email AND USERS.email = textbox_emailBy the time I get to the REMINDER table I am combining all the tables and my query is eight lines long.Question 2 > Is there a way of combining the results of a previous select to access information?---------------------------------------------------------------------------------------------------------------Question 3 > What do you think of my table design? The lines represent one to many relationships. If you can give me any tips on databases please do.Thanks for your time,Padraic
View 2 Replies View RelatedHello all,I am developing a website which may be used by a large number of people in the future and I am concerned about performance.
Is it better to have one table with 50, 000 rows or 5,000 tables with 10 rows each?
Is there a way to divide a table in two if the table reaches a certain size?
Is there a limit on the size of tables?
Is there a limit on the number of tables?
Is it possible to create tables from vb.net?
Is it possible to program checks into sql server? For example, could I delete data that has passed a certain date or send an automated email when a time is reached?
Thanks for your time,Padraic
Hey people
I'd be really grateful if someone can help me with this. Could someone explain the following:
If the following code is executed, it runs instantly:
declare @SellItemID numeric (8,0)
select @SellItemID = 5296979
SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )
However, if I use this WHERE clause instead -
WHERE (@SellItemID = 0 OR s.sell_itm_id = @SellItemID)
- it takes 70 micro seconds. When I join a few more tables into the statement, the difference is 4 seconds!
This is an example of a technique I'm using in loads of places - I only want the statement to return all records if the filter is zero, otherwise the matching record only. I think that by using checking the value of the variable in the WHERE clause, a table scan is used instead of an index. This seems nonsensical since the variable is effectively a constant. Wrapping the entire select statement with an IF or CASE works, but when I've got 10 filters I'd have to 100 select statements.
I DON'T GET IT!! There must be a simple answer, HELP!!
Jo
PS this problem seems to occur both in 6.5 and 7.0