Any Clue To Make This Query Run Faster

Oct 3, 2006

Any help would be really appreciated....
My stored procedure...




CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS



select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,


"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)

FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid



GO


Query plan...

----------------------Query Plan

|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))



The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...

View 14 Replies


ADVERTISEMENT

Help Make Query Run Better (faster)

Mar 20, 2007

I need help makeing the following query run more efficently.


Code:


SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state

View 3 Replies View Related

How Do I Make 30 Sec Running Query (select C1 Sum(x) From T1 Where C1 &&> 1000 Group By C1) Run Faster?

Aug 10, 2007

It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.


Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?

if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?


Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?


Thanks in for you help in advance

View 1 Replies View Related

How Can I Make It Faster?

Apr 17, 2008

I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster?
Thanks for your input.

View 4 Replies View Related

How To Make Insert Faster

Jan 26, 2001

Hi.We have stored procedure update specific table
Each time it run it delete 5000- 6000 rows
from table then insert 5000- 6000 rows with different information.
It take up to 1 1/2 min execute.

1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ?
2. Is any other way increase speed of insert?

View 2 Replies View Related

Can I Make Subreport Run Faster?

Nov 29, 2007



Hi:

I have thousands of rows that I have to retrieve for my report. When I run my main report it returns my data as a reasonable time.

When I run my sub report (by its own) again data are returned in reasonable time.

When I build the sub report in my main report it takes forever (I have to stop). Are there any tricks that I can use to make this run faster?





Thank you in advance

Bahram

View 3 Replies View Related

Would Split Make It Faster ??

Aug 5, 2007

Hi,
Here is the typical processing I do in Data Flow
1) read data file (data source)
2) do something with the data (e.g. derived column, script component, etc)
3) pass data as parameters into OLE DB command (stored proc) to take care of the rest.

Would it be faster if
I split the data into say 10 "chunks" (using split transform). Each chunk will go to its own OLE DB command (10 OLE DB command). Now I will have 10 concurrent processes running the stored proc ?

Am i misunderstanding concurrent process here ?

Thanks

View 1 Replies View Related

Make Stored Proc Faster

Jun 17, 2004

Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:

ALTER PROCEDURE sproc_ReturnAvailability

@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT

AS

IF @96hrPlusFlag = 0

BEGIN
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)
END
ELSE

BEGIN
IF @96hrPlusFlag = 1

SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey)
FROM tbl_SurgerySlot
INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode)
AND tbl_SurgerySlot.ExtractDate = @ExtractDate
AND tbl_SurgerySlot.StartTime >@DateTo
AND tbl_SurgerySlotDescription.NormalBookable = 1
AND tbl_SurgerySlot.SurgerySlotKey NOT IN(
SELECT tbl_Appointment.SurgerySlotKey
FROM tbl_Appointment
WHERE tbl_Appointment.ExtractDate = @ExtractDate
AND tbl_Appointment.Deleted = 0
AND tbl_Appointment.Cancelled = 0
)

END

Cheers...

View 1 Replies View Related

How To Make You Reports Load Faster ?

Sep 3, 2007

Any ideas how to make reports faster when returning lots fo rows?
I know you would need to work on your sql query etc..
Or maybe cache it.
But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself.
Any ideas would be appreciated

View 2 Replies View Related

Indexing SL CE To Make Search Faster. How?

Jan 11, 2006

Dear VB coders!

I have 8.000 or 16.000 products on my list in an sql table. It takes 8 or 16 secs to find the last item.

How could I make the search faster? Indexing? But how?

I created:
createTable1.CommandText = "Create TABLE products (ID int IDENTITY(0,1) PRIMARY KEY, name ntext)"
 
and I use simple WHERE to search.
 
Thank you!

View 3 Replies View Related

Query Would Run For Ever - No Clue

Oct 25, 2005

Hello fellow DBAs

I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.

The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).

I tried the following:

UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG

Nothing helps... what should I do next?

View 1 Replies View Related

I Need A Faster Query

Jun 20, 2007

Hi y'all,
I've recently run a profiler on my code and following query took 7 seconds:
SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC
Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table?
Thanks in advance! I'll let you know how long your query takes :)

View 4 Replies View Related

Faster SQL Query

Mar 31, 2004

Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????

Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???

Question 2:Can anybody give me few examples like this?????? I need them desparetly.

Thanx. Bye.

View 37 Replies View Related

How To Tell If One Query Is Faster Than Another

Jul 26, 2006

I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.

The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.

If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.

Is there anything I can do?

View 5 Replies View Related

Faster Query Results

Oct 17, 2007

Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
 

View 4 Replies View Related

Query Run Time (which Should Be Faster?)

Feb 28, 2008

Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.



INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive
SELECT * FROM DBTest.dbo.tblVendorMaster

View 1 Replies View Related

Better (faster) Way Of Handling Query

Mar 26, 2008



I have a query which will display the employee name who is handling maximum number of project.
the query is



Code Snippet

select EmployeeDetails.Empid,

EmployeeDetails.FirstName + ' '+EmployeeDetails.LastName as EmpName,

count(*) as Number_Of_Projects
from EmployeeDetails left outer join LUP_EmpProject

on EmployeeDetails.Empid= LUP_EmpProject.Empid
group by EmployeeDetails.Empid,EmployeeDetails.FirstName,EmployeeDetails.LastName
HAVING count(*)=(SELECT TOP 1 COUNT(EmpID)



FROM LUP_EmpProject GROUP BY EmpID ORDER BY 1 DESC)






where EmployeeDetails table contain empname and empid. LUP_EmpProject table contain empid and project id.

Can anybody help me in writing a better query than the above given?

Please help!!!!!!!!!!

View 4 Replies View Related

Drop PK And Query Runs Faster. Why?

Aug 23, 2000

I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).

No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.

Any suggestions short and/or long term?

Thanks for any help.

View 1 Replies View Related

AdHoc Query Faster Than Stored Proc?

Aug 28, 2004

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?

View 5 Replies View Related

Why Is DTS Faster Than A Query To Load A Remote Table?

Apr 23, 2007

Hello

DTS is notoriously faster than making the following statement:

insert into synonym_MyRemoteTable
select * from myLocalTable

Why is it so?

Thanks a lot.

View 6 Replies View Related

Faster Query - Select By One Or Multiple Fields

Feb 1, 2008

Let's say I have a table of users.
Let's imagine there's two fields:
username (PK),
password

Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to
(1) SELECT * FROM [User]
WHERE username = 'whatever' AND password='whatever'
or
(2) SELECT * FROM [User]
WHERE username = 'whatever'
and then in my code check that the record returned matched the password?

View 6 Replies View Related

Why Does A Query Runs Considerably Faster Than Stored Procedure?

Dec 29, 2003

I have an stored procedure that takes 23 seconds to execute. However, the same query is executed in 4 seconds.

All the tests where made using the Query Analizer.

What could be wrong?

Thanks a lot in advance.

View 13 Replies View Related

Same Statement Executes 10 Times Faster As Raw Sql In Query Analyzer Then In A Stored Proc

Aug 15, 2007



Hi,


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?


Thank you,


Michael Tzoanos

View 4 Replies View Related

No Clue About This Error

Jul 13, 2006

 No clue whats causing this error,please help
Server Error in '/learn' Application.


Unable to open the physical file "g:inetpubwwwrootlearnApp_DataPersonal.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".An attempt to attach an auto-named database for file g:inetpubwwwrootlearnApp_DataPersonal.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

View 2 Replies View Related

Need To Backup W/o A Clue How To

Dec 10, 2002

I have no idea what I'm doing in SQL Server 2000... honestly no DBMS. I have a book at home but am required to do a backup of about 20 databases today (updating software tonight). Can anyone help me out? I found this (http://www.databasejournal.com/img/backupdatabase.sql) script but do not know how to run it (query analyzer?).

Thanks!

View 1 Replies View Related

Help! No Clue On My Requirement And Where To Start

Jun 28, 2004

Hi,
I am not even sure where and what to do on this requirements

"If the system crashes before all the information during transaction fails, none of those changes will be saved to the database."


what kind of info you need from my DB?
no clue.

thanks

View 1 Replies View Related

Buy A Clue As To How To Iterate Sideways

Sep 14, 2007



hi
i am having a hard time with two kinds of text files that have kind of 'repeating groups' in them...i want to loop it, but dont know how.
one is a text file with a record length of 1200 bytes, but all 95601records are all on one row with no lf, cr or anything else between them, so i cannot feature how to get the forEach container to chop of a Right of claimchunk of 1200 bytes at a time, then go get the next 1200 bytes, because the items aren't stacked, they are adjacent to each other, if you see what i mean.
the other text file has a record lenght of 52 bytes with 28 bytes filler, but this file also goes 'down and across', meaning that here, there are fourteen 'rows' in the file, and they have thousands of lines too, so this one also has to consume all the columns on the row before it moves to the next row.
am i making this harder than it needs to be?
thanks for any light

View 7 Replies View Related

Matrix Report - Need A Clue

May 11, 2007

My data is like this:



QualifiedDate Total DateTimeQualified



2007-05-11 30 5/11/2007 3:12

2007-05-11 29 5/11/2007 6:28

2007-05-10 22 5/10/2007 1:54

2007-05-10 10 5/10/2007 5:55





Report needs to be like this:



2007-05-11 59

2007-05-10 32



with a drill down option to get it broken down my the datetimequalified



I've seen some examples but they are so overblown I can't figure it out



Can someone give me something simple to start with?

View 1 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

Substring (Need Help) , Clue, Hint , Solution....?

Apr 10, 2002

Hi there,

I need to get back the actual characters (without trailing blanks) contained in a char(43) field.
So i tried:
- substring(fieldname,1,len(fieldname)) which yields a 43 char column
- using a variable that contains the actual length in
substring(fieldname,1,@nchar)) the same.
- tricking by concatenating the resulting string with a dummy like "" didn't
work either.
So apparently I am at a dead end.
Anybody a clue, hint or solution????
Thanks to all contributors

View 4 Replies View Related

Clue - Who Dun It? The Data Adapter, DataSet, SqlSelect Etc...

Feb 23, 2007

Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error:
"SQL Server does not exist or access denied. "
 I'm using the 1.1 framework and my hosting company provides MSSql 2005.
Thanks for your time,
Charlie
Here's the code in my cond behind file.
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
 
 
namespace blog
{
            /// <summary>
            /// Summary description for WebForm1.
            /// </summary>
            public class WebForm1 : System.Web.UI.Page
            {
                        protected System.Data.SqlClient.SqlConnection sqlConnect;
                        protected System.Web.UI.WebControls.DataList dList;
                        protected System.Data.SqlClient.SqlDataAdapter DA;
                        protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
                        protected System.Web.UI.WebControls.LinkButton linkAdmin;
                        protected System.Web.UI.WebControls.Image header;
                        protected blog.DS ds1;
           
                        private void Page_Load(object sender, System.EventArgs e)
                        {
                                    DA.Fill(ds1, "blogEntry");
                                    dList.DataBind();
                        }
 
                        #region Web Form Designer generated code
                        override protected void OnInit(EventArgs e)
                        {
                                    //
                                    // CODEGEN: This call is required by the ASP.NET Web Form Designer.
                                    //
                                    InitializeComponent();
                                    base.OnInit(e);
                        }
                       
                        /// <summary>
                        /// Required method for Designer support - do not modify
                        /// the contents of this method with the code editor.
                        /// </summary>
                        private void InitializeComponent()
                        {   
                                    this.sqlConnect = new System.Data.SqlClient.SqlConnection();
                                    this.DA = new System.Data.SqlClient.SqlDataAdapter();
                                    this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
                                    this.ds1 = new blog.DS();
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit();
                                    this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click);
                                    //
                                    // sqlConnection String
                                    //////////////////webserver////////////////
                                    this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";
 
                                    //////////////////local///////////////////
                                    //this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" +
                                                //"ist security info=False;initial catalog=blog";
                                    //
                                    // DA
                                    //
                                    this.DA.SelectCommand = this.sqlSelectCommand1;
                                    this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
                                                                                                                                                                                                                                                                         new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {
                                                           
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        new System.Data.Common.DataColumnMapping("bodyID", "bodyID"),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              new System.Data.Common.DataColumnMapping("linkID", "linkID")})});
                                    //
                                    // sqlSelectCommand1
                                    //
                                    this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC";
                                    this.sqlSelectCommand1.Connection = this.sqlConnect;
                                    //
                                    // ds1
                                    //
                                    this.ds1.DataSetName = "DS";
                                    this.ds1.Locale = new System.Globalization.CultureInfo("en-US");
                                    this.Load += new System.EventHandler(this.Page_Load);
                                    ((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();
 
                        }
                        #endregion
 
                        private void linkAdmin_Click(object sender, System.EventArgs e)
                        {
                                    Server.Transfer("logOn.aspx",true);
                        }
            }
}
 

View 5 Replies View Related

Query Executes Faster In Grid Mode Than In Text Mode!!

Mar 9, 2000

Hello,everyone!!

There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)

Rgds,
Adie

View 1 Replies View Related

Need To Make This Query

Jun 28, 2004

Hi guys, I have a car_race table which has these fields

car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'

I need to know if the car lost the 1st race but won the next race

And example of that table for car_id 1:

car_id 1
race_id 1
b_car_won 'NO'

car_id 1
race_id 2
b_car_won 'YES'

Now this is the tricky part, the database has some data integrity issues, so this can occur:

car_id 1
race_id 1
b_car_won 'NO'

car_id 1
race_id 3
b_car_won 'YES'

So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:

car_id 1
race_id 1
b_car_won 'NO'

car_id 1
race_id 3
b_car_won 'YES'

car_id 1
race_id 4
b_car_won 'YES'

If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.

I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved