|
Hi there,
i have a sql server table there i have thousands of record and when i call it from Stored procedure then it will take very huge time to display records how can i make Fast.
Thanks in Advance
|
|
|
|
|
Returning thousands of rows is usually not the problem.
The problem is usually in the query (your stored procedure).
It might help if you were to post the Stored Procedure?
|
|
|
|
|
Without your query I can only suggest things to try as I had to do something similar just recently
1. Break your query down into sections and find what part is slow
2. Indexing your data.
or like the other poster suggested, post your query
Nagy Vilmos wrote: And eat bacon. Bacon's real important for 'puters.
|
|
|
|
|
The general answer is write more efficient code. Since you haven't posted your code, we can't tell you where to improve it.
|
|
|
|
|
In general you shouldn't be attempting to display "thousands" of records in the first place.
This typically occurs in list box type displays for users who are interested in looking at the records. But those users don't want 'thousands" what they want is a very small subset very likely one record. And they know what they are looking for. So only display the single record they are looking for.
|
|
|
|
|
Posting your query will help us determine your problem, but here are some general tips:
1. Index your table(s) properly.
2. Select only the required rows using a properly constructed WHERE clause.
3. Select only the required columns.
|
|
|
|
|
I managed to duplicate a single record. However I intended to display the duplicate record with different values. For example:
A ABC 065 001 A10111 Sell 54.847500 0 0
T ABC 065 001 A10111 Sell 50.847500 0 1
SELECT
case i.ordStatus
when 'Queued' then 'N'
when 'Filled' then 'A'
when 'Partial Filled' then 'A'
when 'Cancel' then 'C'
end AS 'ordStatus',
f.code AS 'exchCode',
g.code AS 'companyCode',
h.code AS 'branchCode',
c.code AS 'clientCode',
i.transType AS 'transType',
case i.ordStatus
when 'Queued' then ((i.quantity * i.price * i.exchRate) / j.denomination)
when 'Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Partial Filled' then ((i.adjust * i.price * i.exchRate) / j.denomination)
when 'Cancel' then ((i.adjust * i.price * i.exchRate) / j.denomination)
end AS 'ordAmount',
case i.ordStatus
when 'Queued' then 0
when 'Filled' then 0
when 'Partial Filled' then 0
when 'Cancel' then 0
end AS 'matchAmount'
FROM TM_TradingProfile a
LEFT JOIN TM_clientTradingProfile b
ON a.id = b.id
LEFT JOIN TM_Client c
ON c.id = b.TM_client_fk
LEFT JOIN BKL_User d
ON d.id = c.id
JOIN BKL_Authentication e
ON c.id = e.BKL_user_fk
LEFT JOIN RM_Exchange f
ON a.RM_exchange_fk = f.id
LEFT JOIN BKL_Company g
ON a.BKL_company_fk = g.id
LEFT JOIN BKL_Branch h
ON a.BKL_branch_fk=h.id
LEFT JOIN RM_MarketTransaction i
ON a.tradingAccountNumber = i.tradingAccNo
LEFT JOIN bkl_forexexch j
ON i.tradCurr = j.currencyCodeFrom
CROSS JOIN (SELECT 1 UNION ALL SELECT 1) AS T(x)
WHERE a.tradingAccountNumber='ST3273'
Besides, I should be able to display a duplicate record when the ordStatus is filled or partial filled.
Your help is kindly appreciated.
Thank You.
|
|
|
|
|
Can't tell exactly what's wrong but you seem to have too many joins. That cross join... hmmm... I am nos sure what you want to do. Personally, I would use a union operator to duplicate records.
SELECT a.col1, a.col2,... 0 As Flag
FROM myTable a
WHERE ...
UNION
SELECT b.col1,b.col2,... 1 as Flag
FROM myTable b
WHERE ....
|
|
|
|
|
Hi,
I am using dot net and sql server. I am sending my data from the database table to the pdf file. It looks using lots of tables to get data slowing down the program and so I tend to use the view instead of tables. Will it be the good idea to use view and if yes, i have few more questions.
1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
2. If I insert data in the view, will the data be there once i close my program?( I am assuming view as the cache table)
3. what is the difference between using view and actual table ?
4. How the use of view and table different?
Thanks in advance
suchita
|
|
|
|
|
SayamiSuchi wrote: 1. When are we inserting data to the view. Is that when we are inserting data to the actual table or anytime ?
You can't insert data into View.
SayamiSuchi wrote: 3. what is the difference between using view and actual table ?
Difference between View and table[^]
Check this[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
|
Blue_Boy wrote: You can't insert data into View.
Actually, most modern databases support what is called "updatable views". If your view does not contain calculated columns and the base tables have a default defined on the columns not selected in the view, then the view must be updatable.
|
|
|
|
|
|
A "view" is a way of looking at data stored in table(s), therefore using a view instead of the underlying base table will not solve your performance issue. If your query is slow, consider these tips:
1. Index your base table(s).
2. Use a WHERE clause to filter only those rows that is needed.
3. Select only the required columns (don't use a * in the SELECT statement).
|
|
|
|
|
i have create a function in pubs data base
create function show(@temp varchar(11))
returns table
return(select *from author where au_id=@temp)
now i wants to access this function from aspx page using c#
please give me an applicable answer so that i will satisfied
thanks
regards
(manoj bhatt)
|
|
|
|
|
String inputValue;
String sqlText;
SqlConnection connection;
SqlCommand command;
SqlDataReader dataReader;
inputValue="123ABC";
sqlText="SELECT dbo.show(" + inputValue +")";
connection=new SqlConnection(" ...whatever your connection stuff is ");
try
{
connection.Open();
command=new SqlCommand(sqlText,connection);
SqlDataReader dataReader = command.ExecuteReader();
if(dataReader != null)
{
while(dataReader.Read())
{
--get values from dataReader
}
}
}
catch
{
}
|
|
|
|
|
oooh! shiny! SQL injection, here we come!!!!
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
I know.... personally I would have preferred a stored procedure.
|
|
|
|
|
I have a couple of newbie questions for SQL Server. On the SQL Server Management Studio, I used the following script to delete and create a table on the master databse:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[PARTNOTE]
GO
CREATE TABLE [dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I can run this as many times as I want. Both deletion and creation work well. My first question is "After the creation, where is the table creates?" I couldn't find where the table object from Object Explorer.
Now I modify the script to perform the same tasks not on the master, but on "MyDatabase":
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[PARTNOTE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [MyDatabase].[dbo].[PARTNOTE]
GO
CREATE TABLE [MyDatabase].[dbo].[PARTNOTE] (
[NUMBER] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NOTE_NUM] [float] NULL ,
[NOTE] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The creation still works but the deletion does not. It couldn't find where the table was creates.
Could someone please explain what goes wrong in the modified script? Thanks!
Best,
Jun
|
|
|
|
|
After you execute script, refresh Tables node by right click on it and click refresh, it should shows created tables.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks. The table created shows up after the refreshing.
Another question. My modified script
if exists (select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
cannot detect the existence of the table I created. If I just execute
drop table [MyDatabase].[dbo].[WL_HEADU]
GO
it works well. What is a proper query I should make in order to detect the table created in "MyDatabase"?
Best,
Jun
|
|
|
|
|
Look to the if statement. Check what
select * from MyDatabase.dbo.sysobjects where id = object_id(N'[MyDatabase].[dbo].[WL_HEADU]') and OBJECTPROPERTY(id, N'IsUserTable') = 1 gives you when the file is known to exist. I have 2008 and it uses sys.object and I do not remember how to check for exists from prior.
|
|
|
|
|
Figured it out...adding
USE [MyDatabase]
GO
before the query can detect the table created.
Best,
Jun
|
|
|
|
|
That's great 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
The table is on the cloud
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
