Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Problem in Retrieving data from Sql Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 05/04/2013 :  08:09:28  Show Profile  Reply with Quote
im using Stored procedure to Retrieve data..
before im retrieving less than 500 records.. my application is almost perfect...
now i created more that 500 transaction records...
and when the time im gonna retrieve it im having a hang time about.. 30secs to 3mins(max)....

if you have any idea why im experiencing this please do leave a comment...

any help to solve this or giving an idea will be fine and i'd be thankful for that!!!


this is my query(stored Procedure)

quote:

"SELECT ReferenceNumber,
(SELECT TOP (1) MODIFIED_DATE
FROM Table_FOLLOW_UP
WHERE (REFERENCENUMBER = Table_Customer_Record.ID)
ORDER BY ID DESC) AS LAST_FOLLOW_UP_DATE,
(SELECT TOP (1) REMARKS_STATUS
FROM Table_FOLLOW_UP AS Table_FOLLOW_UP_1
WHERE (REFERENCENUMBER = Table_Customer_Record.ID)
ORDER BY ID DESC) AS LAST_FOLLOW_UP_STATUS,
Company_Name, Contact_Person, Email_Address, Line_of_Business, Telephone_No, Mobile_No, Fax_No, Address, Area, Sales_Representative,
Credit_Limit, Remaining_Balance, Website, Terms, Mode_of_payment, Type_of_payment, Local_Service, Site_Service, Member, Sales_End_User, Sales_Corporate,ID, Internet_Cafe, ACTIVE
FROM Table_Customer_Record
ORDER BY ID
"





**im using sql server 2008 r2 and vb.net2010
*** im using STORED PROCEDURE for the interaction of SQL AND VB.NET2010

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/04/2013 :  08:36:48  Show Profile  Reply with Quote
WHat indexes do you have on
Table_FOLLOW_UP
Table_Customer_Record
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 05/04/2013 :  08:42:28  Show Profile  Reply with Quote
if im not mistaken... the index your talking about is the COLUMN that are present from one table to other tables right?



table_follow_up = ReferenceNumber
table_Customer_record = ID

Edited by - Yonkouturko on 05/04/2013 08:43:29
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/04/2013 :  08:48:14  Show Profile  Reply with Quote
No.
Click on the + next to the table
Under that click on the + next to the indexes
There should be a list of indexes on the table if any?

If there are non you could try adding some...
Something like..

CREATE NONCLUSTERED INDEX [idx_Table_FOLLOW_UP_1] ON [dbo].[Table_FOLLOW_UP]
(
REFERENCENUMBER ASC
)
INCLUDE ( MODIFIED_DATE,
REMARKS_STATUS
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Create NONCLUSTERED INDEX [idx_Table_Customer_Record_1] ON [dbo].[Table_Customer_Record]
(ID ASC)
,
INCLUDE
(
Company_Name, Contact_Person, Email_Address, Line_of_Business, Telephone_No, Mobile_No, Fax_No, Address, Area, Sales_Representative,
Credit_Limit, Remaining_Balance, Website, Terms, Mode_of_payment, Type_of_payment, Local_Service, Site_Service, Member, Sales_End_User, Sales_Corporate,ID, Internet_Cafe, ACTIVE
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Edited by - UnemployedInOz on 05/04/2013 08:56:00
Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 05/04/2013 :  08:59:20  Show Profile  Reply with Quote
There is nothing there but these words(PK_Table_Customer_Record(Clustered))and(PK_Table_FOLLOW_UP(Clustered)) and a weird Icon
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/04/2013 :  09:01:12  Show Profile  Reply with Quote
Try adding the index shown above
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 05/04/2013 :  12:53:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Try this
SELECT		tcr.ReferenceNumber,
		f.MODIFIED_DATE AS LAST_FOLLOW_UP_DATE,
		f.REMARKS_STATUS AS LAST_FOLLOW_UP_STATUS,
		tcr.Company_Name,
		tcr.Contact_Person,
		tcr.Email_Address,
		tcr.Line_of_Business,
		tcr.Telephone_No,
		tcr.Mobile_No,
		tcr.Fax_No,
		tcr.Address,
		tcr.Area,
		tcr.Sales_Representative, 
		tcr.Credit_Limit,
		tcr.Remaining_Balance,
		tcr.Website,
		tcr.Terms,
		tcr.Mode_of_payment,
		tcr.Type_of_payment,
		tcr.Local_Service,
		tcr.Site_Service,
		tcr.Member,
		tcr.Sales_End_User,
		tcr.Sales_Corporate,ID,
		tcr.Internet_Cafe,
		tcr.ACTIVE
FROM		dbo.Table_Customer_Record AS tcr
OUTER APPLY	(
			SELECT TOP(1)	x.MODIFIED_DATE,
					x.REMARKS_STATUS
			FROM		dbo.Table_FOLLOW_UP AS x
			WHERE		x.REFERENCENUMBER = tcr.ID
			ORDER BY	x.ID DESC
		) AS f(MODIFIED_DATE, REMARKS_STATUS)
ORDER BY	tcr.ID;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000