I'm rewriting an application that previously relied on a flat (as in one table for all data) MSAccess database. For my rewrite, I'm trying to normalize to the greatest extent possible (via FK/etc.), and am now using SQL Server 2008. For background, the original app was written in Java; the new app is being written in .Net C#. What I'm seeing with the new project is MUCH slower write speeds to the DB.The dev environment is a WXP workstation with 3GB RAM on ESX. Visual Studio and SQLServer 2008 are on the same virtual host. We've done testing splitting these out to seperate boxes, but the performance still wasn't there (was perhaps a slight increase in performance). I know there used to be 'rules' not to run SQL on a virtual box, but from what I've read, virtualization has come far enough along that it's no longer an issue.My assumption is that the problem is the nested SELECT statements in the INSERT statement. I'm doing this because of normalization efforts, but would consider myself an intermediate SQL guy at best, so what I'm really looking for is a better, FASTER way to accomplish what I'm trying to do.OK, so here's the code...scalar = db_mgr.scalarQuery("SELECT COUNT(*) FROM Checks WHERE Tool_Check_ID = '" + check_id + "' AND Tool_ID = " + tool_id);if (scalar == "0")//the check id doesn't exist { string newid = db_mgr.scalarQuery("INSERT INTO Checks (Tool_Check_ID, Tool_ID, Risk_ID) VALUES ('" + check_id + "'," + tool_id + ",5); SELECT id = scope_identity();"); query = "INSERT INTO Vuln_Data (Tool_Check_ID, Inventory_ID, File_Map_ID, Status_ID, Details) VALUES (" + newid + "," + "(SELECT Inventory_ID from Inventory_View WHERE File_Map_Id = " + fmap + " AND IP_Address = '" + ip + "' AND Netbios = '" + netbios + "' AND Platform = '" + os + "')" + "," + fmap + "," + "(SELECT Status_Id FROM Status WHERE Status = 'Open')" + ",'" + details.Replace("'", "''") + "');"; db_mgr.scalarQuery(query); }//end if scalar == 0else { query = "INSERT INTO Vuln_Data (Tool_Check_ID, Inventory_ID, File_Map_ID, Status_ID, Details) VALUES (" + "(SELECT Check_ID FROM Checks WHERE Tool_Check_ID = '" + check_id + "' AND Tool_ID = " + tool_id + ")" + "," + "(SELECT Inventory_ID from Inventory_View WHERE File_Map_Id = " + fmap + " AND IP_Address = '" + ip + "' AND Netbios = '" + netbios + "' AND Platform = '" + os + "')" + "," + fmap + "," + "(SELECT Status_Id FROM Status WHERE Status = 'Open')" + ",'" + details.Replace("'", "''") + "');"; db_mgr.scalarQuery(query); }//end elseAny help would be most appreciated.Chris
Query Seems Slow - INSERT With Nested SELECTS
Posted - 2009-11-24 : 13:46:00
