Click here to Skip to main content
15,904,023 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am running a simple
SQL
select * from table_name
query for the table which has 600 records in it, but it is keep on executing from hours not giving any results. when I execute using top operator with max to 10 it is executing and giving result when I give top 11 the same problem is happening. I have the same table in other DB instance with more than 5000 records there when I executes
SQL
select * from table_name
it is executing quickly, even the view also executing in the other instance no changes in the logic of table and views in both the instances. Although everything is same in both the instances then why I'm encountering this issue in this instance even there is less number of records compared to other instance?. if view is not giving results then I can understand there might be performance issue but the table itself is not giving any result.
Please suggest some solution and explain why is this happening ? Thanks in Advance.

What I have tried:

I have checked the indexes, updated the statistics for the table and checked if there is any other active transaction that might be affecting this query. I checked using
SQL
DBCC OPENTRAN
it is showing SPID 88 is active but when i check that SPID with
SQL
EXEC sp_who2
that transaction is only not available
Posted
Updated 5-Jun-23 2:19am
Comments
Member 15627495 5-Jun-23 6:46am    
hello !

can you show more code please ?
with what you provide we can't undestand...
S Shuaib Ahmed 5-Jun-23 6:53am    
Hi!
select * from SAP.SAP_REQUISITION_HEADERS
this is the table I am executing
below is the table structure
CREATE TABLE [SAP].[SAP_REQUISITION_HEADERS](
[REQUISITION_ID] [numeric](18, 0) IDENTITY(1000,1) NOT NULL,
[REQUISITION_NUMBER] [nvarchar](60) NOT NULL,
[TITLE] [nvarchar](240) NOT NULL,
[REQ_STATUS] [nvarchar](50) NOT NULL,
[PREPARER_ID] [numeric](18, 0) NOT NULL,
[REQ_BU_ID] [numeric](18, 0) NOT NULL,
[DESCRIPTION] [nvarchar](240) NULL,
[NOTE_TO_APPROVER] [nvarchar](1000) NULL,
[EMERGENCY_REQ_FLAG] [nvarchar](1) NULL,
[CAT_ITEM_ID] [numeric](18, 0) NULL,
[IFACE_SOURCE_CODE] [nvarchar](25) NULL,
[IFACE_SOURCE_LINE_ID] [numeric](18, 0) NULL,
[SUBMISSION_DATE] [datetime] NULL,
[APPROVED_DATE] [datetime] NULL,
[APPROVED_BY] [numeric](18, 0) NULL,
[HAS_WITHDRAWN_LINES] [nvarchar](1) NOT NULL,
[HAS_CANCELED_LINES] [nvarchar](1) NOT NULL,
[HAS_RETURNED_LINES] [nvarchar](1) NOT NULL,
[HAS_REJECTED_LINES] [nvarchar](1) NOT NULL,
[HAS_INCOMPLETE_LINES] [nvarchar](1) NOT NULL,
[HAS_PENDING_APPR_LINES] [nvarchar](1) NOT NULL,
[FUNDS_STATUS] [nvarchar](30) NULL,
[REVISION_NO] [numeric](18, 0) NOT NULL,
[FUNDS_CHK_FAIL_WARN_FLAG] [nvarchar](1) NOT NULL,
[BUDGET_CONTROL_ENABLED_FLAG] [nvarchar](1) NOT NULL,
[INSUFFICIENT_FUNDS_FLAG] [nvarchar](1) NOT NULL,
[INTERNAL_TRANSFER_REQ_FLAG] [nvarchar](1) NOT NULL,
[CREATED_BY] [numeric](18, 0) NOT NULL,
[CREATION_DATE] [datetime] NOT NULL,
[LAST_UPDATED_BY] [numeric](18, 0) NOT NULL,
[LAST_UPDATE_DATE] [datetime] NOT NULL,
[LAST_UPDATE_LOGIN] [numeric](18, 0) NOT NULL,
[ENABLE_FLAG] [nvarchar](1) NULL,
[APPROVAL_STATUS] [nvarchar](50) NULL,
[APPROVAL_KEY] [nvarchar](50) NULL,
[APPROVAL_TYPE] [nvarchar](50) NULL,
[CATALOG_TYPE] [nvarchar](100) NULL,
[REQUESTER_ID] [numeric](18, 0) NULL,
[SHIP_TO_LOCATION_ID] [numeric](18, 0) NULL,
[DEFAULT_TAXATION_COUNTRY] [nvarchar](10) NULL,
[SHIP_TO_LOCATION_CODE] [nvarchar](60) NULL
) ON [PRIMARY]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_WITHDRAWN_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_CANCELED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_RETURNED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_REJECTED_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_INCOMPLETE_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [HAS_PENDING_APPR_LINES]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ((0)) FOR [REVISION_NO]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [FUNDS_CHK_FAIL_WARN_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [BUDGET_CONTROL_ENABLED_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [INSUFFICIENT_FUNDS_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('N') FOR [INTERNAL_TRANSFER_REQ_FLAG]
GO

ALTER TABLE [SAP].[SAP_REQUISITION_HEADERS] ADD DEFAULT ('Y') FOR [ENABLE_FLAG]
0x01AA 5-Jun-23 6:46am    
I would try backup/restore the database.
Member 15627495 5-Jun-23 6:56am    
the ms sql Server need a ';' at end of query

it's probably a syntax error
S Shuaib Ahmed 5-Jun-23 6:58am    
I have tried with a ; also no response.

1 solution

A simple SELECT * on a table that has 600 records should return almost immediately. This points to a problem with the database on the server, not any code you're using to execute the query.

Like has been suggested, try to backup the database and restore it to another SqlServer instance. Run the query against the new instance/restored database. If it works as expected, you've got problems with the database on the original server, which is something we can't help with.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900