I have an VB.NET web app which performs a fairly complicated SQL query. It seems in the morning, the 1st time the page is loaded (and query executed) it takes up to 10-15 seconds to complete loading. Sometimes it even times out. However anytime after that, the page loads up (even from another computer) in about 4-5 seconds. Can someone explain the reason for this and how I might fix the load times in the morning?
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates. (e.g. 1 May 2001 and 20 May 2001) And I would like to select those records that are between two times. (e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me? I can see it would be easier if I had the date in one column and the time in the other. Can it be done without doing that?
We recently upgraded from SQL 6.5 to SQL 7. I have a few .sql files that were each running around 5 - 8 minutes under 6.5. These same files now each take over 30 minutes to run. Has anybody had problems with their queries taking longer to run under 7.0? These files are quite large and are comprised of 3 - 4 batches with several queries in each batch. If anybody has any thoughts on the cause please let me know.
I'm in an unfortunate situation. We are posting information using a stored procedure to an outside SQL server connected to through a System DSN on our server (win 2003 server) using php's ODBC functions (we never had any luck connecting directly to the SQL server using php's mssql functions).
Everything is working fine, we can connect, send querys, etc ... but between 1am and 10am we recieve errors when trying to execute queries (though we can connect fine).
Whoops ... forgot to get the error returned before it turned 10am ... I can post it tomorrow
I think the database is being locked, but unfortunately I know very little about MS SQL server
The people who's database we are connecting to are not being helpful ... I was hoping I could get some suggestions on what would be the cause.
Anything you can suggest would be a huge help! Thanks! - Joe
We are having a problem with Query Analyzer not connecting to SQL Server anymore. Sometimes it will, sometimes it won't. Sometimes when it does, then you click on the databases drop-down, it may take a long time to return. Likewise, it may take a long time to open the object browser, or it may open without the database info but with just the "Common Objects" info.
I have multiple SQLDatasources on multiple pages. When I call the Select command for the datasource the query is run multiple times. I was wondering if anyone had any problems like this. The data is being read into listboxes. If you need any more info or have any specific questions, feel free to ask.
Dear Friends, I am in problem & have to solve one query. I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.
I want to know that how much time every emp have attend in the company per day.
Kindly, do reply as soon as possible.
I am enclosing data defination in txt file along with the data in the MS Excel file.
I have two tables in my database. I want to insert date and no.of hours worked on that day for a particular project. So in a week if end user enters 7 dates and hrs for each day...... i have to insert those values into the table against one project only. Can any one please help me out how to run insert query for 7 times (in a week) with different parameters
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active] ([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName]) SELECT --DISTINCT p.SPID, p.LogiName, p.HostName,
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items> from A left join B on B.id_A = A.id left join C on C.id_B = B.idthis now looks like select <many items> from A left join (B join C on C.id_B = B.id ) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc tblGroups - with GroupID, GroupName tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups. What I want is a list of all contacts, with their IDs, names and what groups they are related to:
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
How can I know whether a query or a stored procedure is successfully executed? I mean like in delete case? here is an example
sql_delete = "DELETE FROM UserData where U_ID='" & tempID & "'" rstIDChk.Open sql_delete, cnn, adOpenStatic, adLockOptimistic
How can I make sure that the record is deleted so that I can proceed with other jobs? How can I catch it in the program like from VB? Is there any return like true or false in SQL? :(
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
Hii I am Varun i have a problem with the dynamic stored procedure This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1 (@TeacherID_OnLeave numeric(9), @DateFrom datetime ,@DateTo datetime , @UserID numeric(9) ) AS declare @flag as numeric(9) declare @year as varchar(4) set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0) if( @year=null or len(@year)=0) set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+') begin insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+'); end') else set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave) return @flag --exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+' --') --return @flag --exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
This has got to be a simple one, but I could not find out how I have this simple proc for test purposes only...
ALTER PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0, @intInvestorId int = 0)ASreturn 5
Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question. When I hit this proc in SQL Query Analyzer.. exec RotoTest '123-45-6789', 0, 1 No return value is displayed in Query Analyzer. When I try something like this exec RotoTest '123-45-6789', 0, 1, intRetValue Output (in this situation I get wrong number of arguments) How can I get a return value displayed on my screen in SQL QUERY ANALYZER with out modifying the proc? I do not want to modify the proc in anyway (because I can't), I just want to display the return value
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this 2-1-2006 3-1-2006 4-1-2006 . . . 16-03-2006 17-03-2006 18-03-2006
Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
This is my problem, i have 2 tables: one table that hold data, having id as identity, say table (id, content), the other table have 2 columns: (id, number). The second table number column refer to the id in the first table. i want to build a query that get the data from the first table that correspond to a specific id in the second table, not only this i want to get the previous, the current and the next item.
For example: if table_data, table_info is first and second tables, something that can do it is :
DECLARE @i int
SELECT * FROM table_data WHERE id in (@i -1, @i +1, @i)
The problem here if @i, @i+1 or @i-1 doesn't exist the column will not be returned, i want to get a result similar to
id, content
25 null 26 null 27 #some content#
or
34 #content1# 35 #content2# 36 #content3#
so my problem is that nulls doesn't appear, i thought about using OUTER JOIN, but the problem is that outer join take tables, not (@i -1, @i +1, @i), so if only i can make somehow the outer join use these values, i think it works.
I have encountered some problems creating MDX query. There are two input parameters on report, both dropdown list and from query. The first parameter will check "State", and the second parameter "Store Name" depends on previous' result. If there's no "State" found, I manage to show a "No Data" in the "State" droplist. But with "No Data" in first parameter, the second parameter simply is not enabled. I want the second parameter shows " No Data" as well but not succeed.
Here's code
...
SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS ,
[Store].[Store Name].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube]) ... The query need to look at dataset that contains [store name] on rows, and some measures on columns. It also restricted by parameter @State.
Store Name that satisfies @State will be displayed. But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result. I'd rather like show something rather than nothing. I try codes like ... MEMBER [Store].[Store].[NA] AS '"N/A"' IIF(<Parameter empty>, (SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...), (SELECT ...<Original statement >) ) ... It shows "Subselect support only Column axis".
Any one has an idea? Please help me out Many thanks!
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
I want the following query to return a row even when table 'X' is empty. How would I do this? SELECT TOP 1 @Var1, @Var2, @Var3 from X The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included. When table is empty, it reurn nothing. It only return a row when table is not empty.
Hi i am trying to use this query to pull all the publications stored in the database and all the authors contributing to that publication (1 to many relationship). I am trying to use a sub query so that i can display the results on one row of a gridview (including a consecutive list of all the authors). However i am recieving this error: Incorrect keyword near the word SET. ?
Maybe i need to add a temp column in the sub query to pull all the related authors for a single publication - but i dont know the sql for this? can anyone help?
Thanks SELECT ISNULL(Publication.month, '')+ ' ' + ISNULL(convert(nvarchar, Publication.year), '') as SingleColumn, Publication.publicationID, Publication.title FROM Publication WHERE Publication.publicationID IN (SELECT (convert(nvarchar, Authors.authorName)) FROM Authors INNER JOIN PublicationAuthors ON Authors.authorID = PublicationAuthors.authorID) AND Publication.typeID IN (SELECT PublicationType.typeName FROM PublicationType INNER JOIN PublicationType ON Publication.typeID = PublicationType.typeID
For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews" How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
I run a query in QA which return funny characters (suppose to be chinese characters). I try saved as CSV and open in excel, still it remain as those funny characters... What can I do to get those output into the chinese character. It doesn't need to be in QA... but I need it in Excel. Thanks
