Database

Groulien12-May-11 22:40

Groulien

12-May-11 22:40

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?

Simon_Whale13-May-11 0:41

Simon_Whale

13-May-11 0:41

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.

SilimSayo13-May-11 3:22

13-May-11 3:22

The general answer is write more efficient code. Since you haven't posted your code, we can't tell you where to improve it.

jschell13-May-11 9:03

jschell

13-May-11 9:03

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.

Shameel17-May-11 4:41

Shameel

17-May-11 4:41

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.

Duplicate a record

solomon1300012-May-11 4:28

solomon13000

12-May-11 4:28

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.

Re: Duplicate a record

SilimSayo12-May-11 8:38

12-May-11 8:38

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 ....

View

Dhyanga12-May-11 3:50

Dhyanga

12-May-11 3:50

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

Blue_Boy12-May-11 4:26

Blue_Boy

12-May-11 4:26

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

David Skelly13-May-11 2:22

David Skelly

13-May-11 2:22

Blue_Boy wrote:
You can't insert data into View.

According to this you can:

Modifying Data Through a View[^]

Shameel17-May-11 4:45

Shameel

17-May-11 4:45

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.

SilimSayo12-May-11 5:46

12-May-11 5:46

See this[^]

Shameel17-May-11 4:48

Shameel

17-May-11 4:48

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).

database function

manoj bhatt 11-May-11 6:14

manoj bhatt

11-May-11 6:14

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)

Re: database function

SilimSayo11-May-11 8:08

11-May-11 8:08

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
{

}

Re: database function

Peter_in_278011-May-11 15:33

Peter_in_2780

11-May-11 15:33

oooh! shiny! SQL injection, here we come!!!!

Software rusts. Simon Stephenson, ca 1994.

Re: database function

SilimSayo12-May-11 0:09

Where is my table created...

12-May-11 0:09

I know.... personally I would have preferred a stored procedure.

Jun Du11-May-11 5:00

Jun Du

11-May-11 5:00

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

Blue_Boy11-May-11 5:17

Blue_Boy

11-May-11 5:17

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

Jun Du11-May-11 5:44

Jun Du

11-May-11 5:44

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

Corporal Agarn11-May-11 8:27

Corporal Agarn

11-May-11 8:27

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.

Jun Du11-May-11 6:37

Jun Du

11-May-11 6:37

Figured it out...adding

USE [MyDatabase]
GO

before the query can detect the table created.

Best,
Jun

Blue_Boy11-May-11 20:47

Blue_Boy

11-May-11 20:47

That's great Smile | :)

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

SilimSayo11-May-11 8:31

11-May-11 8:31

The table is on the cloud Smile | :)