Performances In SqlExpress 2005 When Accessing Multiples Databases
Apr 11, 2007
Hello,
I've written this stored proc, which have to collect records in one table in several databases. These tables contains very few records (1 to 10 max). In SQL Server standard edition, this stored proc is executed instantaneously. But in the Express, it could take 1 minute.
Is there a solution to this problem ?
Thanks in advance,
Best regards,
Guy
ALTER procedure [dbo].[SP_CHECK_USERS]
AS
select 'FIDUCIAIRE' as code,moment,nom,machine from MERCATORFIDUCIAIRE.dbo.sessions s1 where (s1.inactif=0) and (s1.nom<>'MercatorIshop')
union select 'TEST1 ' as code,moment,nom,machine from MERCATORTEST1.dbo.sessions s2 where (s2.inactif=0) and (s2.nom<>'MercatorIshop')
union select 'TEST10 ' as code,moment,nom,machine from MERCATORTEST10.dbo.sessions s3 where (s3.inactif=0) and (s3.nom<>'MercatorIshop')
union select 'TEST11 ' as code,moment,nom,machine from MERCATORTEST11.dbo.sessions s4 where (s4.inactif=0) and (s4.nom<>'MercatorIshop')
union select 'TEST12 ' as code,moment,nom,machine from MERCATORTEST12.dbo.sessions s5 where (s5.inactif=0) and (s5.nom<>'MercatorIshop')
union select 'TEST13 ' as code,moment,nom,machine from MERCATORTEST13.dbo.sessions s6 where (s6.inactif=0) and (s6.nom<>'MercatorIshop')
union select 'TEST14 ' as code,moment,nom,machine from MERCATORTEST14.dbo.sessions s7 where (s7.inactif=0) and (s7.nom<>'MercatorIshop')
union select 'TEST15 ' as code,moment,nom,machine from MERCATORTEST15.dbo.sessions s8 where (s8.inactif=0) and (s8.nom<>'MercatorIshop')
union select 'TEST16 ' as code,moment,nom,machine from MERCATORTEST16.dbo.sessions s9 where (s9.inactif=0) and (s9.nom<>'MercatorIshop')
union select 'TEST17 ' as code,moment,nom,machine from MERCATORTEST17.dbo.sessions s10 where (s10.inactif=0) and (s10.nom<>'MercatorIshop')
union select 'TEST18 ' as code,moment,nom,machine from MERCATORTEST18.dbo.sessions s11 where (s11.inactif=0) and (s11.nom<>'MercatorIshop')
union select 'TEST2 ' as code,moment,nom,machine from MERCATORTEST2.dbo.sessions s12 where (s12.inactif=0) and (s12.nom<>'MercatorIshop')
union select 'TEST20 ' as code,moment,nom,machine from MERCATORTEST20.dbo.sessions s13 where (s13.inactif=0) and (s13.nom<>'MercatorIshop')
union select 'TEST21 ' as code,moment,nom,machine from MERCATORTEST21.dbo.sessions s14 where (s14.inactif=0) and (s14.nom<>'MercatorIshop')
union select 'TEST23 ' as code,moment,nom,machine from MERCATORTEST23.dbo.sessions s15 where (s15.inactif=0) and (s15.nom<>'MercatorIshop')
union select 'TEST3 ' as code,moment,nom,machine from MERCATORTEST3.dbo.sessions s16 where (s16.inactif=0) and (s16.nom<>'MercatorIshop')
union select 'TEST4 ' as code,moment,nom,machine from MERCATORTEST4.dbo.sessions s17 where (s17.inactif=0) and (s17.nom<>'MercatorIshop')
union select 'TEST5 ' as code,moment,nom,machine from MERCATORTEST5.dbo.sessions s18 where (s18.inactif=0) and (s18.nom<>'MercatorIshop')
union select 'TEST6 ' as code,moment,nom,machine from MERCATORTEST6.dbo.sessions s19 where (s19.inactif=0) and (s19.nom<>'MercatorIshop')
union select 'TEST7 ' as code,moment,nom,machine from MERCATORTEST7.dbo.sessions s20 where (s20.inactif=0) and (s20.nom<>'MercatorIshop')
union select 'TEST8 ' as code,moment,nom,machine from MERCATORTEST8.dbo.sessions s21 where (s21.inactif=0) and (s21.nom<>'MercatorIshop')
union select 'TEST9 ' as code,moment,nom,machine from MERCATORTEST9.dbo.sessions s22 where (s22.inactif=0) and (s22.nom<>'MercatorIshop')
When building a website, I get two SQL databases auto-generated: ASPNET.MDF and ASPNETDB.mdf - both have a log file associated with them, and I can connect to them locally no problem. However, when I try to connect through my host, I get an error message: "Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'." I think this is because the ASPNET file doesn't show any database objects??? (ASPNETDB has all its objects) My questions: Should I be getting both files autogenerated? And if so: How can I (or should I) get the ASPNET file to rebuild its database objects? This problem has roots in the fact that I deleted the file(s), thinking they were unneccesary (rookie mistake, I know), and then tried various uninstall/re-install schemes, all of which has put me back to this problem of about four days ago. Now that's what I call progress! ;-) I'm using Visual Web Developer Express 2005 with the default SQL Express version. I've looked at the related posts, of course, but don't see anything specific. I think this is the code in question, and it looks OK to me, but I am a rank beginner: System.Web.Util.SecUtility.CheckSchemaVersion(ProviderBase provider, SqlConnection connection, String[] features, String version, Int32& schemaVersionCheck) +367 Thanks in advance. biobot
Hi, I am developing a small asp.net application where I read from a readonly omnis database via odbc then dependant on user input write records to a second database.My first idea was to use an odbc connection to Omnis database and write to ms sql database. There is quite a few queries that need to be made to the Omnis database and I am binding a datagrid to the MS Sql server database. I was hoping to use stored procedures to update the mssql tables. So there will be much opening and closing of connections. Should I go down that road or should i think of using an Access database, with linked table to the Omnis database, then only one connection needs to be made. And I can store my information in the Access database tables. Thanks.
I'm new to databases as you probably can tell. A friend sent me three data bases, which in my mind should have been one database with all the tables included, but he didn't. So my question is, can you pull info from two databases in the same query?
I write the query acessing one database and the result chart must depend on a where predicate based on accessing tables in another database. If this isn't possible, how do I get the tables I need together, that presently reside in differing databases?
Hi everyone, First of all I'd like to say I'm new to the forums, and in fact new to ASP.net. I've had a couple of applications running in VB.net for the last year or so that I now am looking to move on and into ASP.net (main reason is to do with implementation to remote sites in foreign countries). I'm currently following through the "Working with data and ASP.net 2.0" walkthrough on this site, which I must say has been a great help. I'll start with the background stuff first I have my main database on an SQL server. I also have 2 other databases on there - Customer and Employee. The reason for these are that they are shared amongst my VB applications and I kept them as seperate databases so that one database is all that needs to be administered and maintained, and these changes are then seen on both apps (takes away redundant data as well). In my main database, main table, is a field called CustomerID. This value relates to the CustomerID in the Customer table. In my VB app all I do is have the user select a customer name, and then the customer ID value is copied to the CustomerID field of my main table when inserting / updating a row. This is simply accomplished with 2 SQL connections and 2 dataAdapters. I also use 2 dataSets - one for Customer, one for WorkRequests (the main database)... I use ComponentOne stuff so that my grid will show the CustomerName instead of the CustomerID etc, and the same applies when I use my Employee database - I run many dataAdapters to this for things such as Account Manager, Technologist, Creator, UpdatedBy etc etc. Now, I'm new to VS 2005 - my apps were created in VS 2003 and .net 1.1. Can I easily re-create my apps in ASP.net? My primary goal at the moment is to make sure I can load a row, and display the "lookup" values instead of the "integer" value. As my tables are in seperate databases, I would like to know the best way of accomplishing this, if it is of course possible. Help and advice much appriciated. Kind Regards, Luke
Hello all. I have just built another PC (New PC)and have it connected to myOriginal PC via a LAN. I have my SQL Server 2000 installed on the OriginalPC. Both PCs use WinXP Pro OS. I believe I now have to install the ClientTools on the New PC but after that, how do I access the SQL Server that ison the Original PC. The SQL Server is setup with mixed (both WindowsAuthentication and SQL Server Authentication). Just how do I use the SQLQuery Analyzer that I just installed on the New PC to connect to andretreive data from the SQL Databases which are on the Original PC? Thanksin advance for any help, Jim.
Hey, Everyone - I have downloaded two of the sample databases and get the following error when I attempt to open them:
Unable to open the physical file "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAAssets_Maintenance_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)
So what am I doing wrong? The install of these sample databases is simple and straight-forward.
I am trying to determine if it is possible to add transaction support for a SSIS package. Here are the basic tasks:
Read New/Updated records from SQL Server Update changed records in Access Insert new records into Access Update new records in SQL Server with Access key assigned on insert in previous step Reset dirty_flag
I would like to wrap a transaction around this. If the package fails in the task that updates sql server with the Access key, the next time it runs, it duplicates the new records in Access because it did not finish the process. Is it possible to have a transaction wrap this entire process so I can undo the inserts to Access if I fail any subsequent tasks?
I am using VS 2005 to develop a Pocket PC 2003 application and for some reason I dont have the ability to assign my datasource to a SQL Mobile Database?
When opening Server Explorer, right clicking Data Connection, Add New Data Source, Change Data Source, the only options I get are
Microsoft Access Database File Microsoft ODBC Data Source Microsoft SQL Server Microsoft SQL Server Database File Oracle Database <other>
I was trying to follow the tutorial
Step by Step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp) and in that tutorial when they do the exact same thing it shows a screen shot that also lists 'Microsoft SQL Server Mobile Edition'
Any ideas why I dont have that option? FYI, I have SQL Server 2005 and have installed SQL Server 2005 Mobile Edition.
I have several database files, all in the same directory. I want to write SQL statements that access two databases at once. For example, if only one database would be involved, I would write:
SELECT Test_Source_ID_column, Data_column INTO Test_Destination_table FROM Test_Sources_table The above query copies two columns (Test_Source_ID_column and Data_column) from Test_Sources_table into a brand new table called Test_Destination_table. Now the above query is fine for both the old and new tables being in the same database. But what if Test_Sources_table is in file Old.dbf and I want the new table Test_Destination_table to be in New.dbf? Both Old.dbf and New.dbf are in the same directory. Therefore, there is no security issue about using ......etcpasswords. I should therefore be able to use relative paths. So, I also want to know how I can do that select on two databases in the same directory without typing long, long paths. Is there a way to do queries that access multiple database files (all database files being in the exact same directory) AND use relative paths or no paths (and certainly not type a full path)? NOTE 1: I'm using SQLEXPRESS, therefore do not have the SQL Server 2005 tools. I have Visual Studio 2005 Developer Edition with its tools. NOTE 2: One table is accessed through ODBC. The other table is accessed through SQLEXPRESS.
I have around 5 databases with same structure used to store data for different locations and services.
I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.
My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.
I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases. We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
Hallo everbody, I'm having a serious problem with my aspx pages. I developed few pages that allow users to retrieve books information from a database (MS SQL). The database table contains about 5000 records. My queries are very simple, nevertheless the time necessary to perform them is extremely long. I tried to set the time out of the data adapter to 0 and I removed the debug mode (debug="true") from the web.config file. I'm still not able to perform a query. What is wrong with it? Is it something related with the database?
I would like to know which of the following query is the fastest:
select * from customers C where exists (select 0 from ORDERS O where C.Name like 'A%' and O.Charged = 1)
select * from customers C where exists (select 0 from ORDERS O where O.Charged = 1) and C.Name like 'A%'
I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.
I would like to know which of the following query is the fastest:
select * from customers C where exists (select 0 from ORDERS O where C.Name like 'A%' and O.Charged = 1 and O.Customers_Id = C.Id)
select * from customers C where exists (select 0 from ORDERS O where O.Charged = 1 and O.Customers_Id = C.Id) and C.Name like 'A%'
Because the condition C.Name like 'A%' is inside the sub query, I'm wondering if it will be evaluated for each record of the Orders table. Does anyone know if there is a difference of efficiency when this condition is inside or outside the sub query ?
Hi to everyone! I've just started to learn MS SQL Server 2005 and I hope you can give me some advices about what is the best design for the max performances between the two cases:
Case 1: Single table, but not very scalable Table name: GS salary_id (primary key, int) salary_description (varchar) salary_cumulative_1 (int) salary_cumulative_2 (int) .. salary_cumulative_9 (int)
Case 2: Two tables, this removes the limit of having only 9 salary_cumulative field, but will be performance worst? Keep in mind that this table(s) will be queried a lot the whole times when the user use the application.
Hi, I have an ASPNETDB.MDF (asp.net membership/profile db) that was generated for SQL Express, it has user information I need to keep. I have SQLExpress and full SQL Server 2005 running on the server. I want to remove SQL Express from the server. So can I simply do a backup of the SQL Express membership db and restore it to full SQL and change the connection string in my app or do i need to run the membership wizard on full sql and then do the backup / restore? Or is there another way. Thanks
I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.
The software is using a lot of TADOQuery objects which are connected to the same TADOConnection. The TADOQuery having for options : - cursorlocation : useclient - cursortype : static - locktype : optimistic
On the first use of a TADOQuery in order to execute any type of request, the processing is fast. If I am using this same object for others requests, everything is fine and as fast as the first request. But as soon as I am using another TADOQuery, performances are going down.
In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.
It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.
I used the SQL Profiler to see the details and saw some interesting things : the first uses of TADOQuery are of type SQL:BatchCompleted but switching to another TADOquery make it use another type : RPC:Completed. Contents of this RPC can be a declaration or an execution of cursor.
More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing. But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch. So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.
Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside. The problem should then on the side of the client but I can not figure where.
I hope having been as clear as needed ! My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine. So before recoding I wish to continue the analysis.
So does anyone knows why is happening this serverside thing ? And is there a simple way to make it not happen ?
Is it possible to write a single select statement that would pull the following from a table named tblPayments.
I need the 'first payment date and amount', 'last payment date and amount', 'largest payment date and amount', and 'total payments' for each client by a date range.
I'm trying to select DISTINCT records, but I keep getting multiple records returned even though I used DISTINCT. The returned records are below the query. I ommitted a couple end columns for ease of reference. Thanks :) _________________________________________________________________
SELECT distinct USERS.UserID, miaminewspaper.PropertyPrice, convert(datetime,miaminewspaper.FirstInsertDate), dateadd(day, 30, miaminewspaper.FirstInsertDate)as EndDate, 1, convert (int,AdNumber) as Classified, 0 FROM USERS RIGHT OUTER JOIN miaminewspaperON USERS.EmailAddress = miaminewspaper.AdvertiserEmail WHERE miaminewspaper.validAD=1
------ Returns -------- Notice UserID repeats for 1 and 2!!
Hi, I'am begining with sql server 2005 and on my server, i have already 3 sql server instances which have been created. I want to create a new instance an don't know how to add an instance ? Can anyone help me ?
HiIs it possible to return the results of a query so that instead ofhaving say 10 rows its concatenated, egMy query returns 'M' 10 times, can this be returned as 'M M M M M M MM M M'?ThanksLee
How do I select where two columns are the same, but the remaining columns might be different? For example, if I have 4 columns: First, Last, Class, and Year. I want a listing of First, Last, Class and Year but only if the same First, Last has > 1 row (ie that the same person is in the table twice.)
The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".
Any thoughts why this might be and how I can fix this?
I have 3x6 textbox lay out, QTY, Descp, Cost, with submit button with followin code...., insert function has too many function, anyone know how to work thiis type of data entery?? Thanks in advanc.Dim Quntityx() As Integer = {txtQ1.Text, txtQ2.Text, txtQ3.Text, txtQ4.Text, txtQ5.Text, txtQ6.Text} Dim xDex() As String = {txtD1.Text, txtD2.Text, txtD3.Text, txtD4.Text, txtD5.Text, txtD6.Text}Dim xcost() As Decimal = {txtC1.Text, txtC2.Text, txtC3.Text, txtC4.Text, txtC5.Text, txtC6.Text} Dim i As IntegerFor i = 0 To 5 itemInput.InsertParameters.Add("RequestN", RequestN)itemInput.InsertParameters.Add("Description", xDex(i)) itemInput.InsertParameters.Add("Cost", xcost(i))itemInput.InsertParameters.Add("Quantity", Quntityx(i)) itemInput.Insert(i) Next i
Hello all,I develop under vs2005 and sqlExpress. How can I move my database to sql server2005?? I read somewhere that i need to use "sql server management studio express" which i already installed but for some reason I cant attach to a database under my windows XP account (no password for this account). Please help regards,
Hi, I have a web site in asp.net 2003 with sql 2000. Can I run this database with sqlExpress 2005. And what changes would be made in web.config file. This is due to I am going to check it with Window vista. Thanks in advance
