I've developed the application to fetch the data from the mdf file by passing the index field and using the command IRowsetIndex()::Seek.
Consider TableExample with Columns A,B,C,D and index Index1 with column A and Index2 with column B,C,D.
Its working fine when index1 is passed for the indexvalue.But when i pass index2 of the same table,its throwing the error as BADINDEX.
I'm passing the correct keyvalues in the Seek command also.
When you call IRowsetIndex::GetIndexInfo, it allocates memory for an array of DBINDEXCOLUMNDESC structures, which the documentation says to free using IMalloc::Free. However it does not say to free the pColumnId member of each DBINDEXCOLUMNDESC (which is a DBID*), or the pointer members of pColumnId. I think that if you don't do this, you will have a memory leak.
I used the following code to completely free the DBINDEXCOLUMNDESC structures:
Code Block inline void FreeDBIDMembers(DBID &dbid) { switch(dbid.eKind) { case DBKIND_GUID_NAME: case DBKIND_NAME: if(dbid.uName.pwszName != NULL) CoTaskMemFree(dbid.uName.pwszName); break; case DBKIND_PGUID_NAME: if(dbid.uName.pwszName != NULL) CoTaskMemFree(dbid.uName.pwszName); if(dbid.uGuid.pguid != NULL) CoTaskMemFree(dbid.uGuid.pguid); break; case DBKIND_PGUID_PROPID: if(dbid.uGuid.pguid != NULL) CoTaskMemFree(dbid.uGuid.pguid); break; } } // Used to free memory allocated by IRowsetIndex::GetIndexInfo inline void FreeDBINDEXCOLUMNDESC(DBINDEXCOLUMNDESC *pColDesc, ULONG nColumns) { if (!pColDesc) { return; } DBINDEXCOLUMNDESC *pColCur = pColDesc; for(unsigned int i = 0; i < nColumns; ++i, ++pColCur) { if (pColCur->pColumnID) { FreeDBIDMembers(*pColCur->pColumnID); CoTaskMemFree(pColCur->pColumnID); } } CoTaskMemFree(pColDesc); }
I used CoTaskMemFree rather than IMalloc::Free as I am working with Pocket PC. Thanks also to Joao Paulo Figueira who has documented a fix to a similar memory leak, which was my inspiration for finding and fixing this one! If you are using Figueira's CRowsetIndex, part of his adapted ATL OLEDB templates for Pocket PC, then you are likely to have this leaky code too.
Can I use index seek and still keep 'like' 'or' functionality for the following statement?
--prepare test table
create table entity (Id int identity(1,1), FamName varchar(200), LastName varchar(200)) go create clustered index entity_id on entity (id) go create nonclustered index entity_lastName on entity (lastName) go insert into entity select famName,FirstName from table
--test index
declare @LastName varchar(10) set @LastName = 'a'
select FamName from entity where @LastName is null or LastName like @LastName --clustered index scan
I have created a nolock view off a table to prevent locks. I have users coming in through MS Access that have switched their queries to run against the views. Now we are noticing that queries that used to run as a clustered index seek against the table are running as a clustered index scan against the table and performance in the queries has dropped.
Is there any way that the same query that hits the view instead of the table can be made to run faster or at least use the index seek?
I have an MS Access 2003 database from which I want to seek a specific record in a SQL Server Express 2005 database. I can connect to the table and get a recordcount but the recordset.supports (adseek) and recordset.Supports(adIndex) both return false. Any suggestions? Specific code I'm using is as follows:
Dim cnxn As ADODB.Connection Dim strCnxn As String Set cnxn = New ADODB.Connection cnxn.Provider = "sqloledb" strCnxn = "Data Source=SERVERSQLEXPRESS2005;Initial Catalog=RAMPSQL;Integrated Security='SSPI';"
cnxn.Open strCnxn
Set rsWSC = New ADODB.Recordset rsWSC.CursorLocation = adUseServer strSQL = "DailyData" rsWSC.Open strSQL, cnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
I have a frontend Access and backend SQL works fine but when i in my customer table seek in name it takes very very long time . I use ODBC to my connection
The script below may be use to find out what stored procedure uses a specified column from any of the table. This could be helpful in cases you have change a field name of a table and want to find out what stored procedure uses that column.
From what I've read in the docs, ado.net currently supports opening sqlserver ce tables in table-direct mode and performing Seek operations on them(using SqlCeDataReader), but not on the full-blown sql server. Is this(will this be) still true with ado.net 2.0 & sql server 2005?
I am getting error 0x80040E25 when I try to call seek after update on a Recordset opened as (adOpenStatic, adLockOptimistic, adCmdTableDirect)
9 - (13.250) - <2> - *** error in .DbRecordset.cpp, line 908 10 - (13.250) - <2> - ADO_ERRORS FOR pRs = 200a420, seek, err=-2147217883(80040e25) 11 - (13.250) - <2> - ADO_ERROR: E R R O R 1 of 1. 12 - (13.250) - <2> - ADO_ERROR: DESCRIPTION: All HROWs must be released before new ones can be obtained. [,,,,,]. 13 - (13.250) - <2> - ADO_ERROR: NUMBER: 80040E25 14 - (13.250) - <2> - ADO_ERROR: NATIVE_ERROR: 0 15 - (13.250) - <2> - ADO_ERROR: SOURCE: Microsoft SQL Server 2005 Mobile Edition OLE DB Provider
I registered SQL Mobile 3.0 dlls using regsvr32.exe so now I can connect to SQLCE3.0 databases on desktop using plain ADO with such connection string _T("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0; Data Source=") + name of the file
I have not asked this question before as it did not make sense -> there was no official SQLCE3.0 support on desktop. Now, since SQL CE is promiced to be supported on desktop as SQL/E I decided to ask.
If you display the execution plan and run the following:SET STATISTICS IO ONgoSELECT ProductID, SupplierIDFROM ProductsWHERE SupplierID = 1I don't understand how come there is noBookmark Lookup operation happening to get theProductID?I only see an Index Seek happening on SupplierID.There is no composite index SupplierID + ProductIDso what am I not understanding here?Thank you
I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.
Typically, this table is accessed with a query that includes:
SELECT ... FROM SummaryTable WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)
This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.
As I've investigated this issue I also tried:
SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')
When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.
To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
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?
