Commits

Steve Klee  committed e9a0157

* fix for wrong sqldbtypes being specified in parameter collection which was keeping sql from using index seeks when it should have been

  • Participants
  • Parent commits bae23b4

Comments (0)

Files changed (2)

File BsonSql.Driver/BsonSqlCollection.cs

             }
         }
 
+        private IndexSqlCache GetIndexCache(CollectionInstance instance)
+        {
+            IndexSqlCache sqlCache = _indexCache;
+            if (sqlCache == null)
+            {
+                sqlCache = new IndexSqlCache();
+                IndexInstance[] indexes = instance.Indexes;
+
+                sqlCache.DoNothing = !(indexes != null && indexes.Length > 0);
+                Dictionary<string, IndexKey> uniqueList = new Dictionary<string, IndexKey>();
+                StringBuilder insertSql = new StringBuilder(500);
+                StringBuilder allSql = new StringBuilder(500);
+                SqlCommand cmd = new SqlCommand();
+                int count = 0;
+
+                if (!sqlCache.DoNothing)
+                {
+                    foreach (IndexInstance index in indexes)
+                    {
+                        foreach (IndexKey key in index.Keys)
+                        {
+                            if (key.FieldName != "_id" && !uniqueList.ContainsKey(key.FieldName))
+                            {
+                                uniqueList.Add(key.FieldName, key);
+
+                                string parameterName = "@Idx" + count.ToString();
+                                sqlCache.ParameterMap[parameterName] = key;
+                                SqlDbType sqlType = BsonSqlSerializer.GetSqlTypeForBsonType(key.DataType);
+                                if (sqlType == SqlDbType.VarChar && !key.UseVarChar)
+                                    sqlType = SqlDbType.NVarChar;
+
+                                cmd.Parameters.Add(parameterName, sqlType);
+                                count += 1;
+                            }
+                        }
+                    }
+                }
+
+                //build the insert sql
+                //and the normal sql
+                insertSql.AppendLine();
+                insertSql.Append("INSERT INTO dbo." + _configuration.Prefix);
+                insertSql.Append(instance.CollectionName);
+                insertSql.Append("_I (BsonDocumentID, SchemaVersion");
+
+                StringBuilder insertSql2 = new StringBuilder(500);
+                insertSql2.AppendLine();
+                insertSql2.Append("VALUES (@IdxDocumentId,@IdxSchemaVersion");
+
+                allSql.AppendLine();
+                allSql.Append("UPDATE dbo." + _configuration.Prefix);
+                allSql.Append(instance.CollectionName);
+                allSql.Append("_I SET SchemaVersion = @IdxSchemaVersion");
+
+                if (!sqlCache.DoNothing)
+                {
+                    foreach (SqlParameter parameter in cmd.Parameters)
+                    {
+                        IndexKey parameterKey = sqlCache.ParameterMap[parameter.ParameterName];
+                        string columnName = parameterKey.FieldName.Replace(".", "_");
+
+                        insertSql.AppendFormat(",{0}", columnName);
+                        insertSql2.AppendFormat(",{0}", parameter.ParameterName);
+
+                        allSql.AppendFormat(",{0} = {1}", columnName, parameter.ParameterName);
+                    }
+                }
+
+                insertSql.Append(") ");
+                insertSql2.Append(") ");
+                insertSql.Append(insertSql2.ToString());
+
+                allSql.Append(" WHERE BsonDocumentID = @IdxDocumentId");
+                allSql.AppendLine();
+                allSql.AppendLine("IF @@ROWCOUNT = 0");
+                allSql.Append(insertSql.ToString());
+
+                sqlCache.InsertOnlySql = insertSql.ToString();
+                sqlCache.Sql = allSql.ToString();
+                sqlCache.Command = cmd;
+                sqlCache.UniqueList = uniqueList;
+
+                _indexCache = sqlCache;
+            }
+
+            return sqlCache;
+        }
+
 		private void UpdateDocumentIndex(SqlCommand command, StringBuilder sql, BsonDocument document, bool insertOnly)
 		{
 			CollectionInstance instance = GetConfiguration();
-			IndexSqlCache sqlCache = _indexCache;
-			if (sqlCache == null)
-			{
-				sqlCache = new IndexSqlCache();
-				IndexInstance[] indexes = instance.Indexes;
-				
-				sqlCache.DoNothing = !(indexes != null && indexes.Length > 0);
-				Dictionary<string, IndexKey> uniqueList = new Dictionary<string, IndexKey>();
-				StringBuilder insertSql = new StringBuilder(500);
-				StringBuilder allSql = new StringBuilder(500);
-				SqlCommand cmd = new SqlCommand();
-				int count = 0;
-
-				if (!sqlCache.DoNothing)
-				{
-					foreach (IndexInstance index in indexes)
-					{
-						foreach (IndexKey key in index.Keys)
-						{
-							if (key.FieldName != "_id" && !uniqueList.ContainsKey(key.FieldName))
-							{
-								uniqueList.Add(key.FieldName, key);
-
-								string parameterName = "@Idx" + count.ToString();
-								sqlCache.ParameterMap[parameterName] = key;
-								SqlDbType sqlType = BsonSqlSerializer.GetSqlTypeForBsonType(key.DataType);
-								if (sqlType == SqlDbType.VarChar && !key.UseVarChar)
-									sqlType = SqlDbType.NVarChar;
-
-								cmd.Parameters.Add(parameterName, sqlType);
-								count += 1;
-							}
-						}
-					}
-				}
-
-				//build the insert sql
-				//and the normal sql
-				insertSql.AppendLine();
-                insertSql.Append("INSERT INTO dbo." + _configuration.Prefix);
-				insertSql.Append(instance.CollectionName);
-				insertSql.Append("_I (BsonDocumentID, SchemaVersion");
-
-				StringBuilder insertSql2 = new StringBuilder(500);
-				insertSql2.AppendLine();
-				insertSql2.Append("VALUES (@IdxDocumentId,@IdxSchemaVersion");
-
-				allSql.AppendLine();
-                allSql.Append("UPDATE dbo." + _configuration.Prefix);
-				allSql.Append(instance.CollectionName);
-				allSql.Append("_I SET SchemaVersion = @IdxSchemaVersion");
-
-				if (!sqlCache.DoNothing)
-				{
-					foreach (SqlParameter parameter in cmd.Parameters)
-					{
-						IndexKey parameterKey = sqlCache.ParameterMap[parameter.ParameterName];
-						string columnName = parameterKey.FieldName.Replace(".", "_");
-
-						insertSql.AppendFormat(",{0}", columnName);
-						insertSql2.AppendFormat(",{0}", parameter.ParameterName);
-
-						allSql.AppendFormat(",{0} = {1}", columnName, parameter.ParameterName);
-					}
-				}
-
-				insertSql.Append(") ");
-				insertSql2.Append(") ");
-				insertSql.Append(insertSql2.ToString());
-
-				allSql.Append(" WHERE BsonDocumentID = @IdxDocumentId");
-				allSql.AppendLine();
-				allSql.AppendLine("IF @@ROWCOUNT = 0");
-				allSql.Append(insertSql.ToString());
-
-				sqlCache.InsertOnlySql = insertSql.ToString();
-				sqlCache.Sql = allSql.ToString();
-				sqlCache.Command = cmd;
-
-				_indexCache = sqlCache;
-			}
-
+            IndexSqlCache sqlCache = GetIndexCache(instance);
 			
 			//otherwise we need to add some sql here...
 			command.Parameters.Add("@IdxDocumentId", SqlDbType.UniqueIdentifier).Value =  document["_id"].AsGuid;
 			command.Parameters.Add("@IdxSchemaVersion", SqlDbType.TinyInt).Value = instance.SchemaVersion;
 
-			if (!_indexCache.DoNothing)
+            if (!sqlCache.DoNothing)
 			{
 				SqlCommand indexCommand = sqlCache.Command;
 				foreach (SqlParameter parameter in indexCommand.Parameters)
 
 			using (SqlConnection conn = new SqlConnection(_configuration.GetServer(connectionId).ConnectionString))
 			{
-				SqlCommand cmd = query.Command.Clone();
+                SqlCommand cmd = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 				cmd.CommandText = sql;
                 
                 if (_logging)
 
 			using (SqlConnection conn = new SqlConnection(_configuration.GetServer(connectionId).ConnectionString))
 			{
-                SqlCommand cmd = query.Command.Clone();
+                SqlCommand cmd = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 				cmd.CommandText = sql;
 
                 if (_logging)
 
 		private void ExecuteRemoveAgainstSingleConnection(string connectionId, Query query, int limit, SqlTransaction transaction, CollectionInstance instance, string sql)
 		{
-			SqlCommand command = query.Command.Clone();
+            SqlCommand command = query.PrepareCommandForExecution(GetIndexCache(instance).UniqueList);
 			command.CommandText = sql;
 			ExecuteCommandNonQuery(command, connectionId, transaction, true, null, null);
 		}
 			public Dictionary<string, IndexKey> ParameterMap { get; set; }
 			public SqlCommand Command { get; set; }
 			public bool DoNothing { get; set; }
+            public Dictionary<string, IndexKey> UniqueList { get; set; }
 
 			public IndexSqlCache()
 			{

File BsonSql.Driver/Builders/Query.cs

 using MongoDB.Bson;
 using BsonSql.Common;
 using System.Collections.Concurrent;
+using BsonSql.Driver.Configuration;
+using System.Data;
 
 namespace BsonSql.Driver.Builders
 {
 			}
 
 			cloned._command = command;
+            cloned._wrappedValuesForWhereClause = _wrappedValuesForWhereClause;
 
 			return cloned;
 		}
 				return _command;
 			}
 		}
+
+
+        //makes sure parameter types are of the correct type (varchar, nvarchar, int, etc, to avoid unnecessary conversions....)
+        public SqlCommand PrepareCommandForExecution(Dictionary<string, IndexKey> indexedKeys)
+        {
+            SqlCommand command = this.Command.Clone();
+
+            if (command.Parameters.Count == 0 || indexedKeys.Count == 0)
+                return command;
+
+            //otherwise we need to go through all the parameters, if it is an indexed field then check the index data type
+            //and modify the parameter type to the correct type...by default SQL will use nvarchar and if you indexed
+            //a field as varchar the resulting sql will cause an index scan because of the conversion, not allowing you
+            //to fully leverage indexing...
+            
+            
+            foreach (KeyValuePair<string, BsonSqlFieldWrapper> kvp in _wrappedValuesForWhereClause)
+            {
+                SqlParameter parameter = command.Parameters[kvp.Key];
+                
+                BsonSqlFieldWrapper wrapper = kvp.Value;
+                IndexKey indexKey = null;
+                if (indexedKeys.TryGetValue(wrapper.FieldName, out indexKey))
+                {
+                    switch (indexKey.DataType)
+                    {
+                        case BsonType.String:
+                        case BsonType.Array:
+                            parameter.SqlDbType = ((indexKey.UseVarChar) ? SqlDbType.VarChar : SqlDbType.NVarChar);
+                            break;
+                        case BsonType.Int32:
+                            parameter.SqlDbType = SqlDbType.Int;
+                            break;
+                        case BsonType.Int64:
+                            parameter.SqlDbType = SqlDbType.BigInt;
+                            break;
+                    }
+                }
+            }
+
+            return command;
+        }
 	}
 }