.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Strange time issue on simple select statement

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server
 

Hi, I have a strange time out issue on a simple select statement.
This is my select statement:

Select * from DyGeJournal where ord>=4609635 and Fracsec>=31

The ord and Fracsec is the primary key.
When I ask for the actual excecution plan, he is using a an index seek.
It takes more than 1,5 minutes to get a result back.

When I shrink my database, everyting is running as expected, the query above runs in milliseconds.
After a while (2 weeks) it start agian running very slow.

I have the same logic on other tables, but I don't have the problem on those tables.
What can cause this problem?




View Complete Post


More Related Resource Links

NLB - Strange Time out issue with 2 WFEs MOSS 2007 ?

  

Hi, 

 I am having a MOSS 2007 environment with the follo.

     

Having  Two front end servers – each individual  Hyper-V  and   1 Index Server.

These WFEs  are Virtual Servers with Windows 2008 64 bit OS.

I configured the Windows Load balancing.

SQL & JavaScript - Apostrophe Issue, SELECT statement is not returning any results.

  

Hi,

Any help would be great on this.

I have the following code within an .asp page in JavaScript:

function SDsitecalls1(Sitename)

SQL = "SELECT COUNT(*) FROM [SDADMIN].[SRFILE] WHERE SRSITE = 'Our Lady''s R C Sports College - Hbev' AND SRCOMPLETE=0 AND SRINCOMP=1 AND SR_Group=38;"
 tempval=connection.execute(SQL)
 response.write(tempval(0))
end function

The function is called via the below:

 <td style="width: 114px" class="style65"><a href="/suppdesk/customercalls.asp?customer=BSFMAN&site=Our+Lady''s+R+C+Sports+College"><%=SDsitecalls1("Our Lady's R C Sports College - Hbev")%></a></td> 

When this is ran I do not receive any errors but the SELECT command doesn't return any results (Just a 0). I've tried a few different methods such as using the Parameter SiteName and Single/Double Apostrophes however I either receive an error or blank results.

The function works with all other names however the apostrophe seems to cause issues here. Is there a way to extract the data without editing the SQL?

Thanks very much for the help,

Jas


NLB - Strange Time out issue with 2 WFEs MOSS 2007 ?

  

Hi,


 I am having a MOSS 2007 environment with the follo.

     

Having  Two front end servers – each individual  Hyper-V  and   1 Index Server.

These WFEs  are Virtual Servers with Windows 2008 64 bit OS.

I configured the Windows Load balancing.

In these 2 WFEs Two sites are deployed (One is Team site and One Publishing site) both are intranet sites.

 

Now the issue I am facing here is:

 

Assume that Tower1 and Tower2 are 2 different locations.

 We have a VLAN setup  Web sites are working in same VLAN[say Tower1] where Servers are set up without any issue.

But Web sites are giving Time out issue when it is accessed from different location VLAN [Tower2], after some clicks or web site navigations and website will be up after few minutes.

But the site  is working in different VLAN within in same location[Tower1 or Tower2] where server are set up

 

When web site is timing out in different location VLAN [Tower2], web site is timing out in all the locations and we will not able to connect to front end servers remotely. But same time we are able to connect to hyper–V  and from there, able to connect to front end servers, web site is accessible from there without any i

If Statement Issue

  

Hi,

This is very strange to me. I have spent 2hrs looking into this inside out , but dont seem to get it

I have an if statement that inserts data into the database. But i check to make sure the name is not entered twice. Now when the name is already in the db, then it should insert else put out a message saying its already in the db

My code works fine except that  when its not in the db, its inserts but still puts out the message that says "Name is already in use" Not sure if am not seeing something. But thought i did bring it to the forum. Here is my code...Pretty simple

Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Dim objConnection As SqlConnection
        Dim objCommand As SqlCommand
        Dim strSQLQuery1 As String

 

        ' If there's nothing to Add for then don't search
        ' o/w build our SQL Query and execute it.
        If String.IsNullOrEmpty(txtAdd.Text) Or Leadbank.SelectedValue = "000" Then

          

web services transactions (select statement)

  
 Hi everybody, i'm just getting started in web services and i have a problem connecting to a database. All i want is that my web service recieves a string parameter and return a dataset so that in the client aplication i just show the result of the web service in a datagrid. Here's the code of the web service:    <%@ WebService Language="C#" CodeBehind="Service1.asmx.cs" %> using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Linq;using System.Web;using System.Web.Services;using System.Web.Services.Protocols;using System.Xml.Linq;using System.EnterpriseServices;using System.Data.SqlClient;namespace Farmacia{    [WebService(Namespace = "http://localhost/MyWebServices")]    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]    [ToolboxItem(false)]    public class Farmacia : System.Web.Services.WebService    {        private SqlDataAdapter da;        [WebMethod(TransactionOption=TransactionOption.RequiresNew)]        public DataSet Consultas (string sel)        {            if (sel == "")&

PredictCaseLikelihood - strange result for a simple dataset

  
Hi, I have a some numbers between 1 and 431 and I want to use PredictCaseLikelyHood to determine the chances that any new number is a valid member of the existing set. My current dataset looks like this: 1 1 1 2 3 3 3 3 4 5 5 6 7 8 10 10 10 10 11 11 12 13 15 15 20 20 20 20 20 20 20 20 20 20 20 22 25 25 25 25 27 30 30 30 30 35 40 40 42 45 45 50 50 50 50 50 50 50 50 50 50 50 58 60 73 80 80 89 90 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 100 116 140 155 155 200 431   I use clustering to train the model with no holdout set. And I use the Mining Model Prediction Tab to test the PredictCaseLikelihood (normalized) result with a singleton query. As expected, when I enter new numbers between 1 and 431 the returned probability by PredictCaseLikelihood is very high (approx 0.99), however, when I enter positive outliers like 2000, the likelihood is INCREASED to 1, and if I give 2500 and above the likelihood is NaN. Any idea what is causing this strange behaviour? Is my dataset not fit for clustering and in that case why? I use SSAS 2008.   Thanks for any advice, Jørgen      

Time Dimension Enhancement with Business intelligence Issue

  
Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

SQL DIAGOSITC MANAGER tools capture "SELECT *" statement while ALTER / ADD CHECK CONSTRAINT is execu

  
Hello  ,   I exectuted the code(alter/add with check)  below in SSMS . My_table has 200 millions records . While running the alter/add with check query  the server crashed and the “SQL DIAGOSITC MANAGER” which run on the top of sql serever alert the DBA that select * from my_table was obtained from the last command issued by the user using a DBCC command.      Where does the select * from my_table comes from ? I didn’t excuted select * from my_table at all .   After the sql serever re started I was able to run the command below with out any problem. I checked the SQL PROFILER for the trace there is no “select * from my_table” .   So again where this select * from my_table comes from ?   Here is my initial query ----------------------------------------------------------------- ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key1] FOREIGN KEY([Key1]) REFERENCES [Dbo].[Dim_table1] ([Key1]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key1] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key2] FOREIGN KEY([key2]) REFERENCES [Dbo].[Dim_table2] ([key2]) GO   ALTER TABLE [Dbo].my_table CHECK CONSTRAINT [FK_key2] GO   ALTER TABLE [Dbo].my_table WITH CHECK ADD  CONSTRAINT [FK_key3] FOREIGN KEY([d], [c], [r], [g]) REFERENCES [Dbo].my

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

How do i use the select statement in functions?

  
hi friends, i have table-value function, but the has the bug in the body and i cannot debug, becuase it does not allow the select statement in it. is there away to print values to the screen, when debugging? PS: in not using a SP to debug it thanks

Assistance with SELECT statement requested

  
Hi All, I am having trouble getting started on writing a select statement to meet the following requirements.  I have a table with Employee Ids, address types, and address information.  I would like to obtain one record for each EmplID based upon the Type column; the PRF type record if it exists, otherwise then the PRI record.  (See Table A) TableA Rcd #, EmplID, Type, Address Data 1, 000001, PRI, etc 2, 000001, PRF, etc 3, 000002, PRI, etc 4, 000003, PRI, etc 5, 000003, PRF, etc 6, 000003, OTH, etc So, my result set from the table above would be: 2, 000001, PRF, etc 3, 000002, PRI, etc 5, 000003, PRF, etc What is the best way to obtain this information via a SELECT statement?  Thanks in advance, Steve  

Get output from storedprocedure and assign in Select statement

  
Is it possible to get the output from stored procedure and assign into select statement. for example. Here prc_GetComplete is another storeprocedure to get the value for @Completes INSERT INTO TABELE1 (ID, address, completes ) SELECT NEWID(), t2.address, @Completes = EXEC prc_GetComplete t2.ID , @Completes OUT FROM TABELE2 t2   Regards, teesh

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

ODBC Select if Statement in MAS90 query

  
Hi, I am connecting to a MAS90 database via DSN and am having trouble with the query string for ODBC.  I can collect most all fields if I SELECT them directly, however, I want to create a result column based on which cost is higher. As far as I know, this select statement in to and ODBC connection needs to comply with MSSQL I have tried SELECT IM1_InventoryMasterfile.ItemNumber, IFF(IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost,IM1_InventoryMasterfile.LastCost,IM1_InventoryMasterfile.AveCost) as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile And SELECT IM1_InventoryMasterfile.ItemNumber, CASE WHEN IM1_InventoryMasterfile.LastCost > IM1_InventoryMasterfile.AveCost THEN IM1_InventoryMasterfile.LastCost ELSE IM1_InventoryMasterfile.AveCost END as 'Cost', IM1_InventoryMasterfile.TotalQtyOnHand FROM IM1_InventoryMasterfile IM1_InventoryMasterfile Neither are working?  What am I missing here? Can I do this sort of if then statement in MSSQL?   Thanks for your help 

Select random 10 records in sql server 2005 in terms of time

  
Hello,i have a query that select random records in access database in terms of time : "SELECT TOP 10 * FROM Tbl ORDER BY Rnd(-10000000*TimeValue(Now())*[ID])"now i wish to convert this query for using in sql server 2005 such that return random records in term of time 
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend