SQL Server Forums
Profile | Register | 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
 New Topic  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
30265 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000