I have an application that has an Access .mde frontend, linked to sql server tables thru odbc. I have one table that seems to have an index issue.
When a record gets added from the Access form, the record commits but then it seems to lose the table link. #Name? gets displayed in all the fields on the form.
If this is more appropriate in an Access forum please let me know.
The other issue is this: while I have a single user in the Access front end, connected to the sql server database, she tries to write a record to another table and a dialog pops up indicating a WRITE CONFLICT and prompts the user to either drop the changes or copy them to the clip board.
I have indexes set on both tables and I cannot solve this problem. Any suggestions would be great.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this: CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey OR LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?
I am developing a Recruitment Agency WebsiteI am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.Is it anyway possible to extract all the CV Details data by matching the path.I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.Thank you very much in Advance string strCatalog = "TestCatalog", strsearchstrings=""; string strQuery=""; strsearchstrings = txtsearch.Text; strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')"; // TextBox1.Text is the word that you type in the text box to query by using Indexing Service. string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring); conn.Open(); System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn); System.Data.OleDb.OleDbDataReader objRdr; objRdr = objcmd.ExecuteReader(); DataGrid1.DataSource=objRdr; DataGrid1.DataBind(); objRdr.Close(); conn.Close();
I've done a search on this topic and came up with several posts, none helping me too much.
Basically, I"m not searching the contents of my website but rather records in a product table. And I want the users to be able to put something generic as half a movie title or product name.
And so, I want to know if either there's a way to optimize searching tables with the LIKE clause etc. or if it just really comes down to using teh Index Server.
But if there are other implementations or methods out there would someone please let me know? Thanks.
i read about index ,clustered and non clustered index in sql server but did not understant still properly can anybody explain in simple terms,what is it or else any good url,not google but any good url
select count(*) from MyTable where MyDate > '4/7/2001' and MyField = 'y'
I have an index on MyDate. If I run the query without the check on MyField, it uses the index. If I add the criteria that MyField = 'y' the query no longer uses that index and does a clustered index scan (the clustered index is on an identity column - not my choice).
If I use the WITH clause and force it to use the index, performance is drastically improved, but I would REALLY rather not have to force index usage. I've tried running UPDATE STATISTICS, but that didn't help.
It looks like using the indexes causes more reads, but it takes 1/15th of the time (I tested this both with and without caching of the data).
Can someone show me how to create an index on the lrsn field in the below view. This view indexes are totally new to me but are needed due to slow performance.
Thanks DG Hall
CREATE VIEW dbo.V_Comm_Imp_Summary AS SELECT TOP 100 PERCENT cu.Comm_Use, cu.year_built, cu.lrsn, dbo.V_Comm_AG_Area.area AS AG_Area, dbo.V_Comm_Bsmt_Area.area AS BG_Area, cu.extension, dbo.v_CommUse_Description.use_description FROM dbo.V_Comm_Use cu INNER JOIN dbo.v_CommUse_Description ON cu.Comm_Use = dbo.v_CommUse_Description.Comm_Use LEFT OUTER JOIN dbo.V_Comm_Bsmt_Area ON cu.lrsn = dbo.V_Comm_Bsmt_Area.lrsn LEFT OUTER JOIN dbo.V_Comm_AG_Area ON cu.lrsn = dbo.V_Comm_AG_Area.lrsn
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int PK_table1 on Year,Quarter,Hits
If i do a "Select Year From table1 group by Year", the executionplan looks like
select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01 |--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?
select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL |--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01 |--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11 |--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
I have a table which has cluster index on col1 column. If i insert 10 into my table what would be cluster index key value?Is it going to be 10 as well? How do i get cluster index key value?
At the moment I only know enough about SQL Server to create the databases Ineed as a backend for the various internal desktop/web applications I write.I haven't ever had the time to get seriously involved with internal workingsof SQL Server and we are far too small to employ a DBA.With any new application performance isn't a problem as the number ofrecords is very low. Over the life of the application the number of recordsobviously increases and performance can start to suffer. My normal cure isto use the Query Analyzer to suggest and implement indexes that normally getthings back up to speed.I was wondering whether SQL Server automatically keeps the created indexesup to date or do I need to be doing anything else either manually or as ascheduled job? The databases all have auto create statistics and auto updatestatistics enabled.Any pointers gratefully receivedThanksAlan
If I am doing an Index Server query from SQL, such as:SELECTQ.*FROMOPENQUERY(FTIndexPM, ''SELECT path, characterization, rank, hitcountFROM SCOPE('DEEP TRAVERSAL OF .....can anyone tell me which TCP/UDP ports will be used between the SQLServer and the Index server if the Index Server is on another machine?I'm doing the query from SQL so that I can join the results with atable in the database and am not interested in doing the Index queryfrom the app server. I haven't been able to find any info on the netfor which firewall ports are used for this.
